]>
Commit | Line | Data |
---|---|---|
59c06b17 CS |
1 | <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); |
2 | /** | |
3 | * CodeIgniter | |
4 | * | |
5 | * An open source application development framework for PHP 5.1.6 or newer | |
6 | * | |
7 | * @package CodeIgniter | |
8 | * @author ExpressionEngine Dev Team | |
9 | * @copyright Copyright (c) 2008 - 2011, EllisLab, Inc. | |
10 | * @license http://codeigniter.com/user_guide/license.html | |
11 | * @link http://codeigniter.com | |
12 | * @since Version 1.0 | |
13 | * @filesource | |
14 | */ | |
15 | ||
16 | // ------------------------------------------------------------------------ | |
17 | ||
18 | /** | |
19 | * SQLSRV Database Adapter Class | |
20 | * | |
21 | * Note: _DB is an extender class that the app controller | |
22 | * creates dynamically based on whether the active record | |
23 | * class is being used or not. | |
24 | * | |
25 | * @package CodeIgniter | |
26 | * @subpackage Drivers | |
27 | * @category Database | |
28 | * @author ExpressionEngine Dev Team | |
29 | * @link http://codeigniter.com/user_guide/database/ | |
30 | */ | |
31 | class CI_DB_sqlsrv_driver extends CI_DB { | |
32 | ||
33 | var $dbdriver = 'sqlsrv'; | |
34 | ||
35 | // The character used for escaping | |
36 | var $_escape_char = ''; | |
37 | ||
38 | // clause and character used for LIKE escape sequences | |
39 | var $_like_escape_str = " ESCAPE '%s' "; | |
40 | var $_like_escape_chr = '!'; | |
41 | ||
42 | /** | |
43 | * The syntax to count rows is slightly different across different | |
44 | * database engines, so this string appears in each driver and is | |
45 | * used for the count_all() and count_all_results() functions. | |
46 | */ | |
47 | var $_count_string = "SELECT COUNT(*) AS "; | |
48 | var $_random_keyword = ' ASC'; // not currently supported | |
49 | ||
50 | /** | |
51 | * Non-persistent database connection | |
52 | * | |
53 | * @access private called by the base class | |
54 | * @return resource | |
55 | */ | |
56 | function db_connect($pooling = false) | |
57 | { | |
58 | // Check for a UTF-8 charset being passed as CI's default 'utf8'. | |
59 | $character_set = (0 === strcasecmp('utf8', $this->char_set)) ? 'UTF-8' : $this->char_set; | |
60 | ||
61 | $connection = array( | |
62 | 'UID' => empty($this->username) ? '' : $this->username, | |
63 | 'PWD' => empty($this->password) ? '' : $this->password, | |
64 | 'Database' => $this->database, | |
65 | 'ConnectionPooling' => $pooling ? 1 : 0, | |
66 | 'CharacterSet' => $character_set, | |
67 | 'ReturnDatesAsStrings' => 1 | |
68 | ); | |
69 | ||
70 | // If the username and password are both empty, assume this is a | |
71 | // 'Windows Authentication Mode' connection. | |
72 | if(empty($connection['UID']) && empty($connection['PWD'])) { | |
73 | unset($connection['UID'], $connection['PWD']); | |
74 | } | |
75 | ||
76 | return sqlsrv_connect($this->hostname, $connection); | |
77 | } | |
78 | ||
79 | // -------------------------------------------------------------------- | |
80 | ||
81 | /** | |
82 | * Persistent database connection | |
83 | * | |
84 | * @access private called by the base class | |
85 | * @return resource | |
86 | */ | |
87 | function db_pconnect() | |
88 | { | |
89 | $this->db_connect(TRUE); | |
90 | } | |
91 | ||
92 | // -------------------------------------------------------------------- | |
93 | ||
94 | /** | |
95 | * Reconnect | |
96 | * | |
97 | * Keep / reestablish the db connection if no queries have been | |
98 | * sent for a length of time exceeding the server's idle timeout | |
99 | * | |
100 | * @access public | |
101 | * @return void | |
102 | */ | |
103 | function reconnect() | |
104 | { | |
105 | // not implemented in MSSQL | |
106 | } | |
107 | ||
108 | // -------------------------------------------------------------------- | |
109 | ||
110 | /** | |
111 | * Select the database | |
112 | * | |
113 | * @access private called by the base class | |
114 | * @return resource | |
115 | */ | |
116 | function db_select() | |
117 | { | |
118 | return $this->_execute('USE ' . $this->database); | |
119 | } | |
120 | ||
121 | // -------------------------------------------------------------------- | |
122 | ||
123 | /** | |
124 | * Set client character set | |
125 | * | |
126 | * @access public | |
127 | * @param string | |
128 | * @param string | |
129 | * @return resource | |
130 | */ | |
131 | function db_set_charset($charset, $collation) | |
132 | { | |
133 | // @todo - add support if needed | |
134 | return TRUE; | |
135 | } | |
136 | ||
137 | // -------------------------------------------------------------------- | |
138 | ||
139 | /** | |
140 | * Execute the query | |
141 | * | |
142 | * @access private called by the base class | |
143 | * @param string an SQL query | |
144 | * @return resource | |
145 | */ | |
146 | function _execute($sql) | |
147 | { | |
148 | $sql = $this->_prep_query($sql); | |
149 | return sqlsrv_query($this->conn_id, $sql, null, array( | |
150 | 'Scrollable' => SQLSRV_CURSOR_STATIC, | |
151 | 'SendStreamParamsAtExec' => true | |
152 | )); | |
153 | } | |
154 | ||
155 | // -------------------------------------------------------------------- | |
156 | ||
157 | /** | |
158 | * Prep the query | |
159 | * | |
160 | * If needed, each database adapter can prep the query string | |
161 | * | |
162 | * @access private called by execute() | |
163 | * @param string an SQL query | |
164 | * @return string | |
165 | */ | |
166 | function _prep_query($sql) | |
167 | { | |
168 | return $sql; | |
169 | } | |
170 | ||
171 | // -------------------------------------------------------------------- | |
172 | ||
173 | /** | |
174 | * Begin Transaction | |
175 | * | |
176 | * @access public | |
177 | * @return bool | |
178 | */ | |
179 | function trans_begin($test_mode = FALSE) | |
180 | { | |
181 | if ( ! $this->trans_enabled) | |
182 | { | |
183 | return TRUE; | |
184 | } | |
185 | ||
186 | // When transactions are nested we only begin/commit/rollback the outermost ones | |
187 | if ($this->_trans_depth > 0) | |
188 | { | |
189 | return TRUE; | |
190 | } | |
191 | ||
192 | // Reset the transaction failure flag. | |
193 | // If the $test_mode flag is set to TRUE transactions will be rolled back | |
194 | // even if the queries produce a successful result. | |
195 | $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE; | |
196 | ||
197 | return sqlsrv_begin_transaction($this->conn_id); | |
198 | } | |
199 | ||
200 | // -------------------------------------------------------------------- | |
201 | ||
202 | /** | |
203 | * Commit Transaction | |
204 | * | |
205 | * @access public | |
206 | * @return bool | |
207 | */ | |
208 | function trans_commit() | |
209 | { | |
210 | if ( ! $this->trans_enabled) | |
211 | { | |
212 | return TRUE; | |
213 | } | |
214 | ||
215 | // When transactions are nested we only begin/commit/rollback the outermost ones | |
216 | if ($this->_trans_depth > 0) | |
217 | { | |
218 | return TRUE; | |
219 | } | |
220 | ||
221 | return sqlsrv_commit($this->conn_id); | |
222 | } | |
223 | ||
224 | // -------------------------------------------------------------------- | |
225 | ||
226 | /** | |
227 | * Rollback Transaction | |
228 | * | |
229 | * @access public | |
230 | * @return bool | |
231 | */ | |
232 | function trans_rollback() | |
233 | { | |
234 | if ( ! $this->trans_enabled) | |
235 | { | |
236 | return TRUE; | |
237 | } | |
238 | ||
239 | // When transactions are nested we only begin/commit/rollback the outermost ones | |
240 | if ($this->_trans_depth > 0) | |
241 | { | |
242 | return TRUE; | |
243 | } | |
244 | ||
245 | return sqlsrv_rollback($this->conn_id); | |
246 | } | |
247 | ||
248 | // -------------------------------------------------------------------- | |
249 | ||
250 | /** | |
251 | * Escape String | |
252 | * | |
253 | * @access public | |
254 | * @param string | |
255 | * @param bool whether or not the string will be used in a LIKE condition | |
256 | * @return string | |
257 | */ | |
258 | function escape_str($str, $like = FALSE) | |
259 | { | |
260 | // Escape single quotes | |
261 | return str_replace("'", "''", $str); | |
262 | } | |
263 | ||
264 | // -------------------------------------------------------------------- | |
265 | ||
266 | /** | |
267 | * Affected Rows | |
268 | * | |
269 | * @access public | |
270 | * @return integer | |
271 | */ | |
272 | function affected_rows() | |
273 | { | |
274 | return @sqlrv_rows_affected($this->conn_id); | |
275 | } | |
276 | ||
277 | // -------------------------------------------------------------------- | |
278 | ||
279 | /** | |
280 | * Insert ID | |
281 | * | |
282 | * Returns the last id created in the Identity column. | |
283 | * | |
284 | * @access public | |
285 | * @return integer | |
286 | */ | |
287 | function insert_id() | |
288 | { | |
289 | return $this->query('select @@IDENTITY as insert_id')->row('insert_id'); | |
290 | } | |
291 | ||
292 | // -------------------------------------------------------------------- | |
293 | ||
294 | /** | |
295 | * Parse major version | |
296 | * | |
297 | * Grabs the major version number from the | |
298 | * database server version string passed in. | |
299 | * | |
300 | * @access private | |
301 | * @param string $version | |
302 | * @return int16 major version number | |
303 | */ | |
304 | function _parse_major_version($version) | |
305 | { | |
306 | preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $version, $ver_info); | |
307 | return $ver_info[1]; // return the major version b/c that's all we're interested in. | |
308 | } | |
309 | ||
310 | // -------------------------------------------------------------------- | |
311 | ||
312 | /** | |
313 | * Version number query string | |
314 | * | |
315 | * @access public | |
316 | * @return string | |
317 | */ | |
318 | function _version() | |
319 | { | |
320 | $info = sqlsrv_server_info($this->conn_id); | |
321 | return sprintf("select '%s' as ver", $info['SQLServerVersion']); | |
322 | } | |
323 | ||
324 | // -------------------------------------------------------------------- | |
325 | ||
326 | /** | |
327 | * "Count All" query | |
328 | * | |
329 | * Generates a platform-specific query string that counts all records in | |
330 | * the specified database | |
331 | * | |
332 | * @access public | |
333 | * @param string | |
334 | * @return string | |
335 | */ | |
336 | function count_all($table = '') | |
337 | { | |
338 | if ($table == '') | |
339 | return '0'; | |
340 | ||
341 | $query = $this->query("SELECT COUNT(*) AS numrows FROM " . $this->dbprefix . $table); | |
342 | ||
343 | if ($query->num_rows() == 0) | |
344 | return '0'; | |
345 | ||
346 | $row = $query->row(); | |
347 | $this->_reset_select(); | |
348 | return $row->numrows; | |
349 | } | |
350 | ||
351 | // -------------------------------------------------------------------- | |
352 | ||
353 | /** | |
354 | * List table query | |
355 | * | |
356 | * Generates a platform-specific query string so that the table names can be fetched | |
357 | * | |
358 | * @access private | |
359 | * @param boolean | |
360 | * @return string | |
361 | */ | |
362 | function _list_tables($prefix_limit = FALSE) | |
363 | { | |
364 | return "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"; | |
365 | } | |
366 | ||
367 | // -------------------------------------------------------------------- | |
368 | ||
369 | /** | |
370 | * List column query | |
371 | * | |
372 | * Generates a platform-specific query string so that the column names can be fetched | |
373 | * | |
374 | * @access private | |
375 | * @param string the table name | |
376 | * @return string | |
377 | */ | |
378 | function _list_columns($table = '') | |
379 | { | |
380 | return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$this->_escape_table($table)."'"; | |
381 | } | |
382 | ||
383 | // -------------------------------------------------------------------- | |
384 | ||
385 | /** | |
386 | * Field data query | |
387 | * | |
388 | * Generates a platform-specific query so that the column data can be retrieved | |
389 | * | |
390 | * @access public | |
391 | * @param string the table name | |
392 | * @return object | |
393 | */ | |
394 | function _field_data($table) | |
395 | { | |
396 | return "SELECT TOP 1 * FROM " . $this->_escape_table($table); | |
397 | } | |
398 | ||
399 | // -------------------------------------------------------------------- | |
400 | ||
401 | /** | |
402 | * The error message string | |
403 | * | |
404 | * @access private | |
405 | * @return string | |
406 | */ | |
407 | function _error_message() | |
408 | { | |
409 | $error = array_shift(sqlsrv_errors()); | |
410 | return !empty($error['message']) ? $error['message'] : null; | |
411 | } | |
412 | ||
413 | // -------------------------------------------------------------------- | |
414 | ||
415 | /** | |
416 | * The error message number | |
417 | * | |
418 | * @access private | |
419 | * @return integer | |
420 | */ | |
421 | function _error_number() | |
422 | { | |
423 | $error = array_shift(sqlsrv_errors()); | |
424 | return isset($error['SQLSTATE']) ? $error['SQLSTATE'] : null; | |
425 | } | |
426 | ||
427 | // -------------------------------------------------------------------- | |
428 | ||
429 | /** | |
430 | * Escape Table Name | |
431 | * | |
432 | * This function adds backticks if the table name has a period | |
433 | * in it. Some DBs will get cranky unless periods are escaped | |
434 | * | |
435 | * @access private | |
436 | * @param string the table name | |
437 | * @return string | |
438 | */ | |
439 | function _escape_table($table) | |
440 | { | |
441 | return $table; | |
442 | } | |
443 | ||
444 | ||
445 | /** | |
446 | * Escape the SQL Identifiers | |
447 | * | |
448 | * This function escapes column and table names | |
449 | * | |
450 | * @access private | |
451 | * @param string | |
452 | * @return string | |
453 | */ | |
454 | function _escape_identifiers($item) | |
455 | { | |
456 | return $item; | |
457 | } | |
458 | ||
459 | // -------------------------------------------------------------------- | |
460 | ||
461 | /** | |
462 | * From Tables | |
463 | * | |
464 | * This function implicitly groups FROM tables so there is no confusion | |
465 | * about operator precedence in harmony with SQL standards | |
466 | * | |
467 | * @access public | |
468 | * @param type | |
469 | * @return type | |
470 | */ | |
471 | function _from_tables($tables) | |
472 | { | |
473 | if ( ! is_array($tables)) | |
474 | { | |
475 | $tables = array($tables); | |
476 | } | |
477 | ||
478 | return implode(', ', $tables); | |
479 | } | |
480 | ||
481 | // -------------------------------------------------------------------- | |
482 | ||
483 | /** | |
484 | * Insert statement | |
485 | * | |
486 | * Generates a platform-specific insert string from the supplied data | |
487 | * | |
488 | * @access public | |
489 | * @param string the table name | |
490 | * @param array the insert keys | |
491 | * @param array the insert values | |
492 | * @return string | |
493 | */ | |
494 | function _insert($table, $keys, $values) | |
495 | { | |
496 | return "INSERT INTO ".$this->_escape_table($table)." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; | |
497 | } | |
498 | ||
499 | // -------------------------------------------------------------------- | |
500 | ||
501 | /** | |
502 | * Update statement | |
503 | * | |
504 | * Generates a platform-specific update string from the supplied data | |
505 | * | |
506 | * @access public | |
507 | * @param string the table name | |
508 | * @param array the update data | |
509 | * @param array the where clause | |
510 | * @param array the orderby clause | |
511 | * @param array the limit clause | |
512 | * @return string | |
513 | */ | |
514 | function _update($table, $values, $where) | |
515 | { | |
516 | foreach($values as $key => $val) | |
517 | { | |
518 | $valstr[] = $key." = ".$val; | |
519 | } | |
520 | ||
521 | return "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr)." WHERE ".implode(" ", $where); | |
522 | } | |
523 | ||
524 | // -------------------------------------------------------------------- | |
525 | ||
526 | /** | |
527 | * Truncate statement | |
528 | * | |
529 | * Generates a platform-specific truncate string from the supplied data | |
530 | * If the database does not support the truncate() command | |
531 | * This function maps to "DELETE FROM table" | |
532 | * | |
533 | * @access public | |
534 | * @param string the table name | |
535 | * @return string | |
536 | */ | |
537 | function _truncate($table) | |
538 | { | |
539 | return "TRUNCATE ".$table; | |
540 | } | |
541 | ||
542 | // -------------------------------------------------------------------- | |
543 | ||
544 | /** | |
545 | * Delete statement | |
546 | * | |
547 | * Generates a platform-specific delete string from the supplied data | |
548 | * | |
549 | * @access public | |
550 | * @param string the table name | |
551 | * @param array the where clause | |
552 | * @param string the limit clause | |
553 | * @return string | |
554 | */ | |
555 | function _delete($table, $where) | |
556 | { | |
557 | return "DELETE FROM ".$this->_escape_table($table)." WHERE ".implode(" ", $where); | |
558 | } | |
559 | ||
560 | // -------------------------------------------------------------------- | |
561 | ||
562 | /** | |
563 | * Limit string | |
564 | * | |
565 | * Generates a platform-specific LIMIT clause | |
566 | * | |
567 | * @access public | |
568 | * @param string the sql query string | |
569 | * @param integer the number of rows to limit the query to | |
570 | * @param integer the offset value | |
571 | * @return string | |
572 | */ | |
573 | function _limit($sql, $limit, $offset) | |
574 | { | |
575 | $i = $limit + $offset; | |
576 | ||
577 | return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql); | |
578 | } | |
579 | ||
580 | // -------------------------------------------------------------------- | |
581 | ||
582 | /** | |
583 | * Close DB Connection | |
584 | * | |
585 | * @access public | |
586 | * @param resource | |
587 | * @return void | |
588 | */ | |
589 | function _close($conn_id) | |
590 | { | |
591 | @sqlsrv_close($conn_id); | |
592 | } | |
593 | ||
594 | } | |
595 | ||
596 | ||
597 | ||
598 | /* End of file mssql_driver.php */ | |
599 | /* Location: ./system/database/drivers/mssql/mssql_driver.php */ |