1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* Microsoft SQL Server 2008+ connector class. |
4
|
|
|
* |
5
|
|
|
* <h2>Connecting using Windows</h2> |
6
|
|
|
* |
7
|
|
|
* If you've got your website running on Windows, it's highly recommended you |
8
|
|
|
* use Microsoft SQL Server Driver for PHP "sqlsrv". |
9
|
|
|
* |
10
|
|
|
* A complete guide to installing a Windows IIS + PHP + SQL Server web stack can be |
11
|
|
|
* found here: http://doc.silverstripe.org/installation-on-windows-server-manual-iis |
12
|
|
|
* |
13
|
|
|
* @see http://sqlsrvphp.codeplex.com/ |
14
|
|
|
* |
15
|
|
|
* <h2>Connecting using Linux or Mac OS X</h2> |
16
|
|
|
* |
17
|
|
|
* The following commands assume you used the default package manager |
18
|
|
|
* to install PHP with the operating system. |
19
|
|
|
* |
20
|
|
|
* Debian, and Ubuntu: |
21
|
|
|
* <code>apt-get install php5-sybase</code> |
22
|
|
|
* |
23
|
|
|
* Fedora, CentOS and RedHat: |
24
|
|
|
* <code>yum install php-mssql</code> |
25
|
|
|
* |
26
|
|
|
* Mac OS X (MacPorts): |
27
|
|
|
* <code>port install php5-mssql</code> |
28
|
|
|
* |
29
|
|
|
* These packages will install the mssql extension for PHP, as well |
30
|
|
|
* as FreeTDS, which will let you connect to SQL Server. |
31
|
|
|
* |
32
|
|
|
* More information available in the SilverStripe developer wiki: |
33
|
|
|
* @see http://doc.silverstripe.org/modules:mssql |
34
|
|
|
* @see http://doc.silverstripe.org/installation-on-windows-server-manual-iis |
35
|
|
|
* |
36
|
|
|
* References: |
37
|
|
|
* @see http://freetds.org |
38
|
|
|
* |
39
|
|
|
* @package mssql |
40
|
|
|
*/ |
41
|
|
|
class MSSQLDatabase extends SS_Database |
|
|
|
|
42
|
|
|
{ |
43
|
|
|
|
44
|
|
|
/** |
45
|
|
|
* Words that will trigger an error if passed to a SQL Server fulltext search |
46
|
|
|
*/ |
47
|
|
|
public static $noiseWords = array('about', '1', 'after', '2', 'all', 'also', '3', 'an', '4', 'and', '5', 'another', '6', 'any', '7', 'are', '8', 'as', '9', 'at', '0', 'be', '$', 'because', 'been', 'before', 'being', 'between', 'both', 'but', 'by', 'came', 'can', 'come', 'could', 'did', 'do', 'does', 'each', 'else', 'for', 'from', 'get', 'got', 'has', 'had', 'he', 'have', 'her', 'here', 'him', 'himself', 'his', 'how', 'if', 'in', 'into', 'is', 'it', 'its', 'just', 'like', 'make', 'many', 'me', 'might', 'more', 'most', 'much', 'must', 'my', 'never', 'no', 'now', 'of', 'on', 'only', 'or', 'other', 'our', 'out', 'over', 're', 'said', 'same', 'see', 'should', 'since', 'so', 'some', 'still', 'such', 'take', 'than', 'that', 'the', 'their', 'them', 'then', 'there', 'these', 'they', 'this', 'those', 'through', 'to', 'too', 'under', 'up', 'use', 'very', 'want', 'was', 'way', 'we', 'well', 'were', 'what', 'when', 'where', 'which', 'while', 'who', 'will', 'with', 'would', 'you', 'your', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'); |
48
|
|
|
|
49
|
|
|
/** |
50
|
|
|
* Transactions will work with FreeTDS, but not entirely with sqlsrv driver on Windows with MARS enabled. |
51
|
|
|
* TODO: |
52
|
|
|
* - after the test fails with open transaction, the transaction should be rolled back, |
53
|
|
|
* otherwise other tests will break claiming that transaction is still open. |
54
|
|
|
* - figure out SAVEPOINTS |
55
|
|
|
* - READ ONLY transactions |
56
|
|
|
*/ |
57
|
|
|
protected $supportsTransactions = true; |
58
|
|
|
|
59
|
|
|
/** |
60
|
|
|
* Cached flag to determine if full-text is enabled. This is set by |
61
|
|
|
* {@link MSSQLDatabase::fullTextEnabled()} |
62
|
|
|
* |
63
|
|
|
* @var boolean |
64
|
|
|
*/ |
65
|
|
|
protected $fullTextEnabled = null; |
66
|
|
|
|
67
|
|
|
/** |
68
|
|
|
* Set the default collation of the MSSQL nvarchar fields that we create. |
69
|
|
|
* We don't apply this to the database as a whole, so that we can use unicode collations. |
70
|
|
|
* |
71
|
|
|
* @param string $collation |
72
|
|
|
*/ |
73
|
|
|
public static function set_collation($collation) |
74
|
|
|
{ |
75
|
|
|
Config::inst()->update('MSSQLDatabase', 'collation', $collation); |
76
|
|
|
} |
77
|
|
|
|
78
|
|
|
/** |
79
|
|
|
* The default collation of the MSSQL nvarchar fields that we create. |
80
|
|
|
* We don't apply this to the database as a whole, so that we can use |
81
|
|
|
* unicode collations. |
82
|
|
|
* |
83
|
|
|
* @return string |
84
|
|
|
*/ |
85
|
|
|
public static function get_collation() |
86
|
|
|
{ |
87
|
|
|
return Config::inst()->get('MSSQLDatabase', 'collation'); |
88
|
|
|
} |
89
|
|
|
|
90
|
|
|
/** |
91
|
|
|
* Connect to a MS SQL database. |
92
|
|
|
* @param array $parameters An map of parameters, which should include: |
93
|
|
|
* - server: The server, eg, localhost |
94
|
|
|
* - username: The username to log on with |
95
|
|
|
* - password: The password to log on with |
96
|
|
|
* - database: The database to connect to |
97
|
|
|
* - windowsauthentication: Set to true to use windows authentication |
98
|
|
|
* instead of username/password |
99
|
|
|
*/ |
100
|
|
|
public function connect($parameters) |
101
|
|
|
{ |
102
|
|
|
parent::connect($parameters); |
103
|
|
|
|
104
|
|
|
// Configure the connection |
105
|
|
|
$this->query('SET QUOTED_IDENTIFIER ON'); |
106
|
|
|
$this->query('SET TEXTSIZE 2147483647'); |
107
|
|
|
} |
108
|
|
|
|
109
|
|
|
/** |
110
|
|
|
* Checks whether the current SQL Server version has full-text |
111
|
|
|
* support installed and full-text is enabled for this database. |
112
|
|
|
* |
113
|
|
|
* @return boolean |
114
|
|
|
*/ |
115
|
|
|
public function fullTextEnabled() |
116
|
|
|
{ |
117
|
|
|
if ($this->fullTextEnabled === null) { |
118
|
|
|
$this->fullTextEnabled = $this->updateFullTextEnabled(); |
|
|
|
|
119
|
|
|
} |
120
|
|
|
return $this->fullTextEnabled; |
121
|
|
|
} |
122
|
|
|
|
123
|
|
|
/** |
124
|
|
|
* Checks whether the current SQL Server version has full-text |
125
|
|
|
* support installed and full-text is enabled for this database. |
126
|
|
|
* |
127
|
|
|
* @return boolean |
128
|
|
|
*/ |
129
|
|
|
protected function updateFullTextEnabled() |
130
|
|
|
{ |
131
|
|
|
// Check if installed |
132
|
|
|
$isInstalled = $this->query("SELECT fulltextserviceproperty('isfulltextinstalled')")->value(); |
133
|
|
|
if (!$isInstalled) { |
134
|
|
|
return false; |
135
|
|
|
} |
136
|
|
|
|
137
|
|
|
// Check if current database is enabled |
138
|
|
|
$database = $this->getSelectedDatabase(); |
139
|
|
|
$enabledForDb = $this->preparedQuery( |
140
|
|
|
"SELECT is_fulltext_enabled FROM sys.databases WHERE name = ?", |
141
|
|
|
array($database) |
142
|
|
|
)->value(); |
143
|
|
|
return $enabledForDb; |
144
|
|
|
} |
145
|
|
|
|
146
|
|
|
public function supportsCollations() |
147
|
|
|
{ |
148
|
|
|
return true; |
149
|
|
|
} |
150
|
|
|
|
151
|
|
|
public function supportsTimezoneOverride() |
152
|
|
|
{ |
153
|
|
|
return true; |
154
|
|
|
} |
155
|
|
|
|
156
|
|
|
public function getDatabaseServer() |
157
|
|
|
{ |
158
|
|
|
return "sqlsrv"; |
159
|
|
|
} |
160
|
|
|
|
161
|
|
|
public function selectDatabase($name, $create = false, $errorLevel = E_USER_ERROR) |
162
|
|
|
{ |
163
|
|
|
$this->fullTextEnabled = null; |
164
|
|
|
|
165
|
|
|
return parent::selectDatabase($name, $create, $errorLevel); |
166
|
|
|
} |
167
|
|
|
|
168
|
|
|
public function clearTable($table) |
169
|
|
|
{ |
170
|
|
|
$this->query("TRUNCATE TABLE \"$table\""); |
171
|
|
|
} |
172
|
|
|
|
173
|
|
|
/** |
174
|
|
|
* SQL Server uses CURRENT_TIMESTAMP for the current date/time. |
175
|
|
|
*/ |
176
|
|
|
public function now() |
177
|
|
|
{ |
178
|
|
|
return 'CURRENT_TIMESTAMP'; |
179
|
|
|
} |
180
|
|
|
|
181
|
|
|
/** |
182
|
|
|
* Returns the database-specific version of the random() function |
183
|
|
|
*/ |
184
|
|
|
public function random() |
185
|
|
|
{ |
186
|
|
|
return 'RAND()'; |
187
|
|
|
} |
188
|
|
|
|
189
|
|
|
/** |
190
|
|
|
* The core search engine configuration. |
191
|
|
|
* Picks up the fulltext-indexed tables from the database and executes search on all of them. |
192
|
|
|
* Results are obtained as ID-ClassName pairs which is later used to reconstruct the DataObjectSet. |
193
|
|
|
* |
194
|
|
|
* @param array classesToSearch computes all descendants and includes them. Check is done via WHERE clause. |
195
|
|
|
* @param string $keywords Keywords as a space separated string |
196
|
|
|
* @return object DataObjectSet of result pages |
197
|
|
|
*/ |
198
|
|
|
public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "Relevance DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false) |
199
|
|
|
{ |
200
|
|
|
if (isset($objects)) { |
|
|
|
|
201
|
|
|
$results = new ArrayList($objects); |
202
|
|
|
} else { |
203
|
|
|
$results = new ArrayList(); |
204
|
|
|
} |
205
|
|
|
|
206
|
|
|
if (!$this->fullTextEnabled()) { |
207
|
|
|
return $results; |
|
|
|
|
208
|
|
|
} |
209
|
|
|
if (!in_array(substr($sortBy, 0, 9), array('"Relevanc', 'Relevance'))) { |
210
|
|
|
user_error("Non-relevance sort not supported.", E_USER_ERROR); |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
$allClassesToSearch = array(); |
214
|
|
|
foreach ($classesToSearch as $class) { |
215
|
|
|
$allClassesToSearch = array_merge($allClassesToSearch, array_values(ClassInfo::dataClassesFor($class))); |
216
|
|
|
} |
217
|
|
|
$allClassesToSearch = array_unique($allClassesToSearch); |
218
|
|
|
|
219
|
|
|
//Get a list of all the tables and columns we'll be searching on: |
220
|
|
|
$fulltextColumns = $this->query('EXEC sp_help_fulltext_columns'); |
221
|
|
|
$queries = array(); |
222
|
|
|
|
223
|
|
|
// Sort the columns back into tables. |
224
|
|
|
$tables = array(); |
225
|
|
|
foreach ($fulltextColumns as $column) { |
226
|
|
|
// Skip extension tables. |
227
|
|
|
if (substr($column['TABLE_NAME'], -5) == '_Live' || substr($column['TABLE_NAME'], -9) == '_versions') { |
228
|
|
|
continue; |
229
|
|
|
} |
230
|
|
|
|
231
|
|
|
// Add the column to table. |
232
|
|
|
$table = &$tables[$column['TABLE_NAME']]; |
233
|
|
|
if (!$table) { |
234
|
|
|
$table = array($column['FULLTEXT_COLUMN_NAME']); |
235
|
|
|
} else { |
236
|
|
|
array_push($table, $column['FULLTEXT_COLUMN_NAME']); |
237
|
|
|
} |
238
|
|
|
} |
239
|
|
|
|
240
|
|
|
// Create one query per each table, $columns not used. We want just the ID and the ClassName of the object from this query. |
241
|
|
|
foreach ($tables as $tableName => $columns) { |
242
|
|
|
$baseClass = ClassInfo::baseDataClass($tableName); |
243
|
|
|
|
244
|
|
|
$join = $this->fullTextSearchMSSQL($tableName, $keywords); |
245
|
|
|
if (!$join) { |
246
|
|
|
return $results; |
|
|
|
|
247
|
|
|
} // avoid "Null or empty full-text predicate" |
248
|
|
|
|
249
|
|
|
// Check if we need to add ShowInSearch |
250
|
|
|
$where = null; |
251
|
|
|
if (strpos($tableName, 'SiteTree') === 0) { |
252
|
|
|
$where = array("\"$tableName\".\"ShowInSearch\"!=0"); |
253
|
|
|
} elseif (strpos($tableName, 'File') === 0) { |
254
|
|
|
// File.ShowInSearch was added later, keep the database driver backwards compatible |
255
|
|
|
// by checking for its existence first |
256
|
|
|
$fields = $this->fieldList($tableName); |
|
|
|
|
257
|
|
|
if (array_key_exists('ShowInSearch', $fields)) { |
258
|
|
|
$where = array("\"$tableName\".\"ShowInSearch\"!=0"); |
259
|
|
|
} |
260
|
|
|
} |
261
|
|
|
|
262
|
|
|
$queries[$tableName] = DataList::create($tableName)->where($where, '')->dataQuery()->query(); |
|
|
|
|
263
|
|
|
$queries[$tableName]->setOrderBy(array()); |
264
|
|
|
|
265
|
|
|
// Join with CONTAINSTABLE, a full text searcher that includes relevance factor |
266
|
|
|
$queries[$tableName]->setFrom(array("\"$tableName\" INNER JOIN $join AS \"ft\" ON \"$tableName\".\"ID\"=\"ft\".\"KEY\"")); |
267
|
|
|
// Join with the base class if needed, as we want to test agains the ClassName |
268
|
|
|
if ($tableName != $baseClass) { |
269
|
|
|
$queries[$tableName]->setFrom("INNER JOIN \"$baseClass\" ON \"$baseClass\".\"ID\"=\"$tableName\".\"ID\""); |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
$queries[$tableName]->setSelect(array("\"$tableName\".\"ID\"")); |
273
|
|
|
$queries[$tableName]->selectField("'$tableName'", 'Source'); |
274
|
|
|
$queries[$tableName]->selectField('Rank', 'Relevance'); |
275
|
|
|
if ($extraFilter) { |
276
|
|
|
$queries[$tableName]->addWhere($extraFilter); |
277
|
|
|
} |
278
|
|
|
if (count($allClassesToSearch)) { |
279
|
|
|
$classesPlaceholder = DB::placeholders($allClassesToSearch); |
280
|
|
|
$queries[$tableName]->addWhere(array( |
281
|
|
|
"\"$baseClass\".\"ClassName\" IN ($classesPlaceholder)" => |
282
|
|
|
$allClassesToSearch |
283
|
|
|
)); |
284
|
|
|
} |
285
|
|
|
// Reset the parameters that would get in the way |
286
|
|
|
} |
287
|
|
|
|
288
|
|
|
// Generate SQL |
289
|
|
|
$querySQLs = array(); |
290
|
|
|
$queryParameters = array(); |
291
|
|
|
foreach ($queries as $query) { |
292
|
|
|
$querySQLs[] = $query->sql($parameters); |
|
|
|
|
293
|
|
|
$queryParameters = array_merge($queryParameters, $parameters); |
|
|
|
|
294
|
|
|
} |
295
|
|
|
|
296
|
|
|
// Unite the SQL |
297
|
|
|
$fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY $sortBy"; |
298
|
|
|
|
299
|
|
|
// Perform the search |
300
|
|
|
$result = $this->preparedQuery($fullQuery, $queryParameters); |
301
|
|
|
|
302
|
|
|
// Regenerate DataObjectSet - watch out, numRecords doesn't work on sqlsrv driver on Windows. |
303
|
|
|
$current = -1; |
304
|
|
|
$objects = array(); |
305
|
|
|
foreach ($result as $row) { |
306
|
|
|
$current++; |
307
|
|
|
|
308
|
|
|
// Select a subset for paging |
309
|
|
|
if ($current >= $start && $current < $start + $pageLength) { |
310
|
|
|
$objects[] = DataObject::get_by_id($row['Source'], $row['ID']); |
311
|
|
|
} |
312
|
|
|
} |
313
|
|
|
|
314
|
|
|
if (isset($objects)) { |
315
|
|
|
$results = new ArrayList($objects); |
316
|
|
|
} else { |
317
|
|
|
$results = new ArrayList(); |
318
|
|
|
} |
319
|
|
|
$list = new PaginatedList($results); |
320
|
|
|
$list->setPageStart($start); |
321
|
|
|
$list->setPageLength($pageLength); |
322
|
|
|
$list->setTotalItems($current+1); |
323
|
|
|
return $list; |
324
|
|
|
} |
325
|
|
|
|
326
|
|
|
/** |
327
|
|
|
* Allow auto-increment primary key editing on the given table. |
328
|
|
|
* Some databases need to enable this specially. |
329
|
|
|
* |
330
|
|
|
* @param $table The name of the table to have PK editing allowed on |
331
|
|
|
* @param $allow True to start, false to finish |
332
|
|
|
*/ |
333
|
|
|
public function allowPrimaryKeyEditing($table, $allow = true) |
334
|
|
|
{ |
335
|
|
|
$this->query("SET IDENTITY_INSERT \"$table\" " . ($allow ? "ON" : "OFF")); |
336
|
|
|
} |
337
|
|
|
|
338
|
|
|
/** |
339
|
|
|
* Returns a SQL fragment for querying a fulltext search index |
340
|
|
|
* |
341
|
|
|
* @param $tableName specific - table name |
342
|
|
|
* @param $keywords string The search query |
343
|
|
|
* @param $fields array The list of field names to search on, or null to include all |
344
|
|
|
* |
345
|
|
|
* @returns null if keyword set is empty or the string with JOIN clause to be added to SQL query |
346
|
|
|
*/ |
347
|
|
|
public function fullTextSearchMSSQL($tableName, $keywords, $fields = null) |
348
|
|
|
{ |
349
|
|
|
// Make sure we are getting an array of fields |
350
|
|
|
if (isset($fields) && !is_array($fields)) { |
351
|
|
|
$fields = array($fields); |
352
|
|
|
} |
353
|
|
|
|
354
|
|
|
// Strip unfriendly characters, SQLServer "CONTAINS" predicate will crash on & and | and ignore others anyway. |
355
|
|
|
if (function_exists('mb_ereg_replace')) { |
356
|
|
|
$keywords = mb_ereg_replace('[^\w\s]', '', trim($keywords)); |
357
|
|
|
} else { |
358
|
|
|
$keywords = $this->escapeString(str_replace(array('&', '|', '!', '"', '\''), '', trim($keywords))); |
359
|
|
|
} |
360
|
|
|
|
361
|
|
|
// Remove stopwords, concat with ANDs |
362
|
|
|
$keywordList = explode(' ', $keywords); |
363
|
|
|
$keywordList = $this->removeStopwords($keywordList); |
364
|
|
|
|
365
|
|
|
// remove any empty values from the array |
366
|
|
|
$keywordList = array_filter($keywordList); |
367
|
|
|
if (empty($keywordList)) { |
368
|
|
|
return null; |
369
|
|
|
} |
370
|
|
|
|
371
|
|
|
$keywords = implode(' AND ', $keywordList); |
372
|
|
|
if ($fields) { |
373
|
|
|
$fieldNames = '"' . implode('", "', $fields) . '"'; |
374
|
|
|
} else { |
375
|
|
|
$fieldNames = "*"; |
376
|
|
|
} |
377
|
|
|
|
378
|
|
|
return "CONTAINSTABLE(\"$tableName\", ($fieldNames), '$keywords')"; |
379
|
|
|
} |
380
|
|
|
|
381
|
|
|
/** |
382
|
|
|
* Remove stopwords that would kill a MSSQL full-text query |
383
|
|
|
* |
384
|
|
|
* @param array $keywords |
385
|
|
|
* |
386
|
|
|
* @return array $keywords with stopwords removed |
387
|
|
|
*/ |
388
|
|
|
public function removeStopwords($keywords) |
389
|
|
|
{ |
390
|
|
|
$goodKeywords = array(); |
391
|
|
|
foreach ($keywords as $keyword) { |
392
|
|
|
if (in_array($keyword, self::$noiseWords)) { |
393
|
|
|
continue; |
394
|
|
|
} |
395
|
|
|
$goodKeywords[] = trim($keyword); |
396
|
|
|
} |
397
|
|
|
return $goodKeywords; |
398
|
|
|
} |
399
|
|
|
|
400
|
|
|
/** |
401
|
|
|
* Does this database support transactions? |
402
|
|
|
*/ |
403
|
|
|
public function supportsTransactions() |
404
|
|
|
{ |
405
|
|
|
return $this->supportsTransactions; |
406
|
|
|
} |
407
|
|
|
|
408
|
|
|
/** |
409
|
|
|
* This is a quick lookup to discover if the database supports particular extensions |
410
|
|
|
* Currently, MSSQL supports no extensions |
411
|
|
|
* |
412
|
|
|
* @param array $extensions List of extensions to check for support of. The key of this array |
413
|
|
|
* will be an extension name, and the value the configuration for that extension. This |
414
|
|
|
* could be one of partitions, tablespaces, or clustering |
415
|
|
|
* @return boolean Flag indicating support for all of the above |
416
|
|
|
*/ |
417
|
|
|
public function supportsExtensions($extensions = array('partitions', 'tablespaces', 'clustering')) |
418
|
|
|
{ |
419
|
|
|
if (isset($extensions['partitions'])) { |
420
|
|
|
return false; |
421
|
|
|
} elseif (isset($extensions['tablespaces'])) { |
422
|
|
|
return false; |
423
|
|
|
} elseif (isset($extensions['clustering'])) { |
424
|
|
|
return false; |
425
|
|
|
} else { |
426
|
|
|
return false; |
427
|
|
|
} |
428
|
|
|
} |
429
|
|
|
|
430
|
|
|
/** |
431
|
|
|
* Start transaction. READ ONLY not supported. |
432
|
|
|
*/ |
433
|
|
|
public function transactionStart($transactionMode = false, $sessionCharacteristics = false) |
434
|
|
|
{ |
435
|
|
|
if ($this->connector instanceof SQLServerConnector) { |
436
|
|
|
$this->connector->transactionStart(); |
437
|
|
|
} else { |
438
|
|
|
$this->query('BEGIN TRANSACTION'); |
439
|
|
|
} |
440
|
|
|
} |
441
|
|
|
|
442
|
|
|
public function transactionSavepoint($savepoint) |
443
|
|
|
{ |
444
|
|
|
$this->query("SAVE TRANSACTION \"$savepoint\""); |
445
|
|
|
} |
446
|
|
|
|
447
|
|
|
public function transactionRollback($savepoint = false) |
448
|
|
|
{ |
449
|
|
|
if ($savepoint) { |
450
|
|
|
$this->query("ROLLBACK TRANSACTION \"$savepoint\""); |
451
|
|
|
} elseif ($this->connector instanceof SQLServerConnector) { |
452
|
|
|
$this->connector->transactionRollback(); |
453
|
|
|
} else { |
454
|
|
|
$this->query('ROLLBACK TRANSACTION'); |
455
|
|
|
} |
456
|
|
|
} |
457
|
|
|
|
458
|
|
|
public function transactionEnd($chain = false) |
459
|
|
|
{ |
460
|
|
|
if ($this->connector instanceof SQLServerConnector) { |
461
|
|
|
$this->connector->transactionEnd(); |
462
|
|
|
} else { |
463
|
|
|
$this->query('COMMIT TRANSACTION'); |
464
|
|
|
} |
465
|
|
|
} |
466
|
|
|
|
467
|
|
|
public function comparisonClause($field, $value, $exact = false, $negate = false, $caseSensitive = null, $parameterised = false) |
468
|
|
|
{ |
469
|
|
|
if ($exact) { |
470
|
|
|
$comp = ($negate) ? '!=' : '='; |
471
|
|
|
} else { |
472
|
|
|
$comp = 'LIKE'; |
473
|
|
|
if ($negate) { |
474
|
|
|
$comp = 'NOT ' . $comp; |
475
|
|
|
} |
476
|
|
|
} |
477
|
|
|
|
478
|
|
|
// Field definitions are case insensitive by default, |
479
|
|
|
// change used collation for case sensitive searches. |
480
|
|
|
$collateClause = ''; |
481
|
|
|
if ($caseSensitive === true) { |
482
|
|
|
if (self::get_collation()) { |
483
|
|
|
$collation = preg_replace('/_CI_/', '_CS_', self::get_collation()); |
484
|
|
|
} else { |
485
|
|
|
$collation = 'Latin1_General_CS_AS'; |
486
|
|
|
} |
487
|
|
|
$collateClause = ' COLLATE ' . $collation; |
488
|
|
|
} elseif ($caseSensitive === false) { |
489
|
|
|
if (self::get_collation()) { |
490
|
|
|
$collation = preg_replace('/_CS_/', '_CI_', self::get_collation()); |
491
|
|
|
} else { |
492
|
|
|
$collation = 'Latin1_General_CI_AS'; |
493
|
|
|
} |
494
|
|
|
$collateClause = ' COLLATE ' . $collation; |
495
|
|
|
} |
496
|
|
|
|
497
|
|
|
$clause = sprintf("%s %s %s", $field, $comp, $parameterised ? '?' : "'$value'"); |
498
|
|
|
if ($collateClause) { |
499
|
|
|
$clause .= $collateClause; |
500
|
|
|
} |
501
|
|
|
|
502
|
|
|
return $clause; |
503
|
|
|
} |
504
|
|
|
|
505
|
|
|
/** |
506
|
|
|
* Function to return an SQL datetime expression for MSSQL |
507
|
|
|
* used for querying a datetime in a certain format |
508
|
|
|
* |
509
|
|
|
* @param string $date to be formated, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' |
510
|
|
|
* @param string $format to be used, supported specifiers: |
511
|
|
|
* %Y = Year (four digits) |
512
|
|
|
* %m = Month (01..12) |
513
|
|
|
* %d = Day (01..31) |
514
|
|
|
* %H = Hour (00..23) |
515
|
|
|
* %i = Minutes (00..59) |
516
|
|
|
* %s = Seconds (00..59) |
517
|
|
|
* %U = unix timestamp, can only be used on it's own |
518
|
|
|
* @return string SQL datetime expression to query for a formatted datetime |
519
|
|
|
*/ |
520
|
|
|
public function formattedDatetimeClause($date, $format) |
521
|
|
|
{ |
522
|
|
|
preg_match_all('/%(.)/', $format, $matches); |
523
|
|
|
foreach ($matches[1] as $match) { |
524
|
|
|
if (array_search($match, array('Y', 'm', 'd', 'H', 'i', 's', 'U')) === false) { |
525
|
|
|
user_error('formattedDatetimeClause(): unsupported format character %' . $match, E_USER_WARNING); |
526
|
|
|
} |
527
|
|
|
} |
528
|
|
|
|
529
|
|
View Code Duplication |
if (preg_match('/^now$/i', $date)) { |
|
|
|
|
530
|
|
|
$date = "CURRENT_TIMESTAMP"; |
531
|
|
|
} elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date)) { |
532
|
|
|
$date = "'$date.000'"; |
533
|
|
|
} |
534
|
|
|
|
535
|
|
|
if ($format == '%U') { |
536
|
|
|
return "DATEDIFF(s, '1970-01-01 00:00:00', DATEADD(hour, DATEDIFF(hour, GETDATE(), GETUTCDATE()), $date))"; |
537
|
|
|
} |
538
|
|
|
|
539
|
|
|
$trans = array( |
540
|
|
|
'Y' => 'yy', |
541
|
|
|
'm' => 'mm', |
542
|
|
|
'd' => 'dd', |
543
|
|
|
'H' => 'hh', |
544
|
|
|
'i' => 'mi', |
545
|
|
|
's' => 'ss', |
546
|
|
|
); |
547
|
|
|
|
548
|
|
|
$strings = array(); |
549
|
|
|
$buffer = $format; |
550
|
|
|
while (strlen($buffer)) { |
551
|
|
|
if (substr($buffer, 0, 1) == '%') { |
552
|
|
|
$f = substr($buffer, 1, 1); |
553
|
|
|
$flen = $f == 'Y' ? 4 : 2; |
554
|
|
|
$strings[] = "RIGHT('0' + CAST(DATEPART({$trans[$f]},$date) AS VARCHAR), $flen)"; |
555
|
|
|
$buffer = substr($buffer, 2); |
556
|
|
|
} else { |
557
|
|
|
$pos = strpos($buffer, '%'); |
558
|
|
|
if ($pos === false) { |
559
|
|
|
$strings[] = $buffer; |
560
|
|
|
$buffer = ''; |
561
|
|
|
} else { |
562
|
|
|
$strings[] = "'".substr($buffer, 0, $pos)."'"; |
563
|
|
|
$buffer = substr($buffer, $pos); |
564
|
|
|
} |
565
|
|
|
} |
566
|
|
|
} |
567
|
|
|
|
568
|
|
|
return '(' . implode(' + ', $strings) . ')'; |
569
|
|
|
} |
570
|
|
|
|
571
|
|
|
/** |
572
|
|
|
* Function to return an SQL datetime expression for MSSQL. |
573
|
|
|
* used for querying a datetime addition |
574
|
|
|
* |
575
|
|
|
* @param string $date, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' |
|
|
|
|
576
|
|
|
* @param string $interval to be added, use the format [sign][integer] [qualifier], e.g. -1 Day, +15 minutes, +1 YEAR |
577
|
|
|
* supported qualifiers: |
578
|
|
|
* - years |
579
|
|
|
* - months |
580
|
|
|
* - days |
581
|
|
|
* - hours |
582
|
|
|
* - minutes |
583
|
|
|
* - seconds |
584
|
|
|
* This includes the singular forms as well |
585
|
|
|
* @return string SQL datetime expression to query for a datetime (YYYY-MM-DD hh:mm:ss) which is the result of the addition |
586
|
|
|
*/ |
587
|
|
|
public function datetimeIntervalClause($date, $interval) |
588
|
|
|
{ |
589
|
|
|
$trans = array( |
590
|
|
|
'year' => 'yy', |
591
|
|
|
'month' => 'mm', |
592
|
|
|
'day' => 'dd', |
593
|
|
|
'hour' => 'hh', |
594
|
|
|
'minute' => 'mi', |
595
|
|
|
'second' => 'ss', |
596
|
|
|
); |
597
|
|
|
|
598
|
|
|
$singularinterval = preg_replace('/(year|month|day|hour|minute|second)s/i', '$1', $interval); |
599
|
|
|
|
600
|
|
|
if ( |
601
|
|
|
!($params = preg_match('/([-+]\d+) (\w+)/i', $singularinterval, $matches)) || |
602
|
|
|
!isset($trans[strtolower($matches[2])]) |
603
|
|
|
) { |
604
|
|
|
user_error('datetimeIntervalClause(): invalid interval ' . $interval, E_USER_WARNING); |
605
|
|
|
} |
606
|
|
|
|
607
|
|
View Code Duplication |
if (preg_match('/^now$/i', $date)) { |
|
|
|
|
608
|
|
|
$date = "CURRENT_TIMESTAMP"; |
609
|
|
|
} elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date)) { |
610
|
|
|
$date = "'$date'"; |
611
|
|
|
} |
612
|
|
|
|
613
|
|
|
return "CONVERT(VARCHAR, DATEADD(" . $trans[strtolower($matches[2])] . ", " . (int)$matches[1] . ", $date), 120)"; |
614
|
|
|
} |
615
|
|
|
|
616
|
|
|
/** |
617
|
|
|
* Function to return an SQL datetime expression for MSSQL. |
618
|
|
|
* used for querying a datetime substraction |
619
|
|
|
* |
620
|
|
|
* @param string $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' |
|
|
|
|
621
|
|
|
* @param string $date2 to be substracted of $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' |
622
|
|
|
* @return string SQL datetime expression to query for the interval between $date1 and $date2 in seconds which is the result of the substraction |
623
|
|
|
*/ |
624
|
|
|
public function datetimeDifferenceClause($date1, $date2) |
625
|
|
|
{ |
626
|
|
View Code Duplication |
if (preg_match('/^now$/i', $date1)) { |
|
|
|
|
627
|
|
|
$date1 = "CURRENT_TIMESTAMP"; |
628
|
|
|
} elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date1)) { |
629
|
|
|
$date1 = "'$date1'"; |
630
|
|
|
} |
631
|
|
|
|
632
|
|
View Code Duplication |
if (preg_match('/^now$/i', $date2)) { |
|
|
|
|
633
|
|
|
$date2 = "CURRENT_TIMESTAMP"; |
634
|
|
|
} elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date2)) { |
635
|
|
|
$date2 = "'$date2'"; |
636
|
|
|
} |
637
|
|
|
|
638
|
|
|
return "DATEDIFF(s, $date2, $date1)"; |
639
|
|
|
} |
640
|
|
|
} |
641
|
|
|
|
You can fix this by adding a namespace to your class:
When choosing a vendor namespace, try to pick something that is not too generic to avoid conflicts with other libraries.