1: <?php
  2: 
  3: /**
  4:  * This file is part of the "dibi" - smart database abstraction layer.
  5:  *
  6:  * Copyright (c) 2005 David Grudl (http://davidgrudl.com)
  7:  *
  8:  * For the full copyright and license information, please view
  9:  * the file license.txt that was distributed with this source code.
 10:  */
 11: 
 12: 
 13: /**
 14:  * The dibi driver for MS SQL Driver 2005 database.
 15:  *
 16:  * Driver options:
 17:  *   - host => the MS SQL server host name. It can also include a port number (hostname:port)
 18:  *   - username (or user)
 19:  *   - password (or pass)
 20:  *   - database => the database name to select
 21:  *   - options (array) => connection options {@link http://msdn.microsoft.com/en-us/library/cc296161(SQL.90).aspx}
 22:  *   - charset => character encoding to set (default is UTF-8)
 23:  *   - resource (resource) => existing connection resource
 24:  *   - lazy, profiler, result, substitutes, ... => see DibiConnection options
 25:  *
 26:  * @author     David Grudl
 27:  * @package    dibi\drivers
 28:  */
 29: class DibiMsSql2005Driver extends DibiObject implements IDibiDriver, IDibiResultDriver
 30: {
 31:     /** @var resource  Connection resource */
 32:     private $connection;
 33: 
 34:     /** @var resource  Resultset resource */
 35:     private $resultSet;
 36: 
 37:     /** @var int|FALSE  Affected rows */
 38:     private $affectedRows = FALSE;
 39: 
 40: 
 41: 
 42:     /**
 43:      * @throws DibiNotSupportedException
 44:      */
 45:     public function __construct()
 46:     {
 47:         if (!extension_loaded('sqlsrv')) {
 48:             throw new DibiNotSupportedException("PHP extension 'sqlsrv' is not loaded.");
 49:         }
 50:     }
 51: 
 52: 
 53: 
 54:     /**
 55:      * Connects to a database.
 56:      * @return void
 57:      * @throws DibiException
 58:      */
 59:     public function connect(array &$config)
 60:     {
 61:         DibiConnection::alias($config, 'options|UID', 'username');
 62:         DibiConnection::alias($config, 'options|PWD', 'password');
 63:         DibiConnection::alias($config, 'options|Database', 'database');
 64:         DibiConnection::alias($config, 'options|CharacterSet', 'charset');
 65: 
 66:         if (isset($config['resource'])) {
 67:             $this->connection = $config['resource'];
 68: 
 69:         } else {
 70:             $this->connection = sqlsrv_connect($config['host'], (array) $config['options']);
 71:         }
 72: 
 73:         if (!is_resource($this->connection)) {
 74:             $info = sqlsrv_errors();
 75:             throw new DibiDriverException($info[0]['message'], $info[0]['code']);
 76:         }
 77:     }
 78: 
 79: 
 80: 
 81:     /**
 82:      * Disconnects from a database.
 83:      * @return void
 84:      */
 85:     public function disconnect()
 86:     {
 87:         sqlsrv_close($this->connection);
 88:     }
 89: 
 90: 
 91: 
 92:     /**
 93:      * Executes the SQL query.
 94:      * @param  string      SQL statement.
 95:      * @return IDibiResultDriver|NULL
 96:      * @throws DibiDriverException
 97:      */
 98:     public function query($sql)
 99:     {
100:         $this->affectedRows = FALSE;
101:         $res = sqlsrv_query($this->connection, $sql);
102: 
103:         if ($res === FALSE) {
104:             $info = sqlsrv_errors();
105:             throw new DibiDriverException($info[0]['message'], $info[0]['code'], $sql);
106: 
107:         } elseif (is_resource($res)) {
108:             $this->affectedRows = sqlsrv_rows_affected($res);
109:             return $this->createResultDriver($res);
110:         }
111:     }
112: 
113: 
114: 
115:     /**
116:      * Gets the number of affected rows by the last INSERT, UPDATE or DELETE query.
117:      * @return int|FALSE  number of rows or FALSE on error
118:      */
119:     public function getAffectedRows()
120:     {
121:         return $this->affectedRows;
122:     }
123: 
124: 
125: 
126:     /**
127:      * Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.
128:      * @return int|FALSE  int on success or FALSE on failure
129:      */
130:     public function getInsertId($sequence)
131:     {
132:         $res = sqlsrv_query($this->connection, 'SELECT @@IDENTITY');
133:         if (is_resource($res)) {
134:             $row = sqlsrv_fetch_array($res, SQLSRV_FETCH_NUMERIC);
135:             return $row[0];
136:         }
137:         return FALSE;
138:     }
139: 
140: 
141: 
142:     /**
143:      * Begins a transaction (if supported).
144:      * @param  string  optional savepoint name
145:      * @return void
146:      * @throws DibiDriverException
147:      */
148:     public function begin($savepoint = NULL)
149:     {
150:         $this->query('BEGIN TRANSACTION');
151:     }
152: 
153: 
154: 
155:     /**
156:      * Commits statements in a transaction.
157:      * @param  string  optional savepoint name
158:      * @return void
159:      * @throws DibiDriverException
160:      */
161:     public function commit($savepoint = NULL)
162:     {
163:         $this->query('COMMIT');
164:     }
165: 
166: 
167: 
168:     /**
169:      * Rollback changes in a transaction.
170:      * @param  string  optional savepoint name
171:      * @return void
172:      * @throws DibiDriverException
173:      */
174:     public function rollback($savepoint = NULL)
175:     {
176:         $this->query('ROLLBACK');
177:     }
178: 
179: 
180: 
181:     /**
182:      * Returns the connection resource.
183:      * @return mixed
184:      */
185:     public function getResource()
186:     {
187:         return $this->connection;
188:     }
189: 
190: 
191: 
192:     /**
193:      * Returns the connection reflector.
194:      * @return IDibiReflector
195:      */
196:     public function getReflector()
197:     {
198:         throw new DibiNotSupportedException;
199:     }
200: 
201: 
202: 
203:     /**
204:      * Result set driver factory.
205:      * @param  resource
206:      * @return IDibiResultDriver
207:      */
208:     public function createResultDriver($resource)
209:     {
210:         $res = clone $this;
211:         $res->resultSet = $resource;
212:         return $res;
213:     }
214: 
215: 
216: 
217:     /********************* SQL ****************d*g**/
218: 
219: 
220: 
221:     /**
222:      * Encodes data for use in a SQL statement.
223:      * @param  mixed     value
224:      * @param  string    type (dibi::TEXT, dibi::BOOL, ...)
225:      * @return string    encoded value
226:      * @throws InvalidArgumentException
227:      */
228:     public function escape($value, $type)
229:     {
230:         switch ($type) {
231:         case dibi::TEXT:
232:         case dibi::BINARY:
233:             return "'" . str_replace("'", "''", $value) . "'";
234: 
235:         case dibi::IDENTIFIER:
236:             // @see http://msdn.microsoft.com/en-us/library/ms176027.aspx
237:             return '[' . str_replace(array('[', ']'), array('[[', ']]'), $value) . ']';
238: 
239:         case dibi::BOOL:
240:             return $value ? 1 : 0;
241: 
242:         case dibi::DATE:
243:             return $value instanceof DateTime ? $value->format("'Y-m-d'") : date("'Y-m-d'", $value);
244: 
245:         case dibi::DATETIME:
246:             return $value instanceof DateTime ? $value->format("'Y-m-d H:i:s'") : date("'Y-m-d H:i:s'", $value);
247: 
248:         default:
249:             throw new InvalidArgumentException('Unsupported type.');
250:         }
251:     }
252: 
253: 
254: 
255:     /**
256:      * Encodes string for use in a LIKE statement.
257:      * @param  string
258:      * @param  int
259:      * @return string
260:      */
261:     public function escapeLike($value, $pos)
262:     {
263:         $value = strtr($value, array("'" => "''", '%' => '[%]', '_' => '[_]', '[' => '[[]'));
264:         return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
265:     }
266: 
267: 
268: 
269:     /**
270:      * Decodes data from result set.
271:      * @param  string    value
272:      * @param  string    type (dibi::BINARY)
273:      * @return string    decoded value
274:      * @throws InvalidArgumentException
275:      */
276:     public function unescape($value, $type)
277:     {
278:         if ($type === dibi::BINARY) {
279:             return $value;
280:         }
281:         throw new InvalidArgumentException('Unsupported type.');
282:     }
283: 
284: 
285: 
286:     /**
287:      * Injects LIMIT/OFFSET to the SQL query.
288:      * @param  string &$sql  The SQL query that will be modified.
289:      * @param  int $limit
290:      * @param  int $offset
291:      * @return void
292:      */
293:     public function applyLimit(&$sql, $limit, $offset)
294:     {
295:         // offset support is missing
296:         if ($limit >= 0) {
297:             $sql = 'SELECT TOP ' . (int) $limit . ' * FROM (' . $sql . ')';
298:         }
299: 
300:         if ($offset) {
301:             throw new DibiNotImplementedException('Offset is not implemented.');
302:         }
303:     }
304: 
305: 
306: 
307:     /********************* result set ****************d*g**/
308: 
309: 
310: 
311:     /**
312:      * Automatically frees the resources allocated for this result set.
313:      * @return void
314:      */
315:     public function __destruct()
316:     {
317:         $this->resultSet && @$this->free();
318:     }
319: 
320: 
321: 
322:     /**
323:      * Returns the number of rows in a result set.
324:      * @return int
325:      */
326:     public function getRowCount()
327:     {
328:         throw new DibiNotSupportedException('Row count is not available for unbuffered queries.');
329:     }
330: 
331: 
332: 
333:     /**
334:      * Fetches the row at current position and moves the internal cursor to the next position.
335:      * @param  bool     TRUE for associative array, FALSE for numeric
336:      * @return array    array on success, nonarray if no next record
337:      */
338:     public function fetch($assoc)
339:     {
340:         return sqlsrv_fetch_array($this->resultSet, $assoc ? SQLSRV_FETCH_ASSOC : SQLSRV_FETCH_NUMERIC);
341:     }
342: 
343: 
344: 
345:     /**
346:      * Moves cursor position without fetching row.
347:      * @param  int      the 0-based cursor pos to seek to
348:      * @return boolean  TRUE on success, FALSE if unable to seek to specified record
349:      */
350:     public function seek($row)
351:     {
352:         throw new DibiNotSupportedException('Cannot seek an unbuffered result set.');
353:     }
354: 
355: 
356: 
357:     /**
358:      * Frees the resources allocated for this result set.
359:      * @return void
360:      */
361:     public function free()
362:     {
363:         sqlsrv_free_stmt($this->resultSet);
364:         $this->resultSet = NULL;
365:     }
366: 
367: 
368: 
369:     /**
370:      * Returns metadata for all columns in a result set.
371:      * @return array
372:      */
373:     public function getResultColumns()
374:     {
375:         $count = sqlsrv_num_fields($this->resultSet);
376:         $columns = array();
377:         for ($i = 0; $i < $count; $i++) {
378:             $row = (array) sqlsrv_field_metadata($this->resultSet, $i);
379:             $columns[] = array(
380:                 'name' => $row['Name'],
381:                 'fullname' => $row['Name'],
382:                 'nativetype' => $row['Type'],
383:             );
384:         }
385:         return $columns;
386:     }
387: 
388: 
389: 
390:     /**
391:      * Returns the result set resource.
392:      * @return mixed
393:      */
394:     public function getResultResource()
395:     {
396:         return $this->resultSet;
397:     }
398: 
399: }
400: