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