git.maemo.org Git - speedfreak/blob - Server/system/libraries/drivers/Database/Mssql.php

   1 <?php defined('SYSPATH') OR die('No direct access allowed.');
   2 /**
   3  * MSSQL Database Driver
   4  *
   5  * @package    Core
   6  * @author     Kohana Team
   7  * @copyright  (c) 2007-2008 Kohana Team
   8  * @license    http://kohanaphp.com/license.html
   9  */
  10 class Database_Mssql_Driver extends Database_Driver
  11 {
  12         /**
  13          * Database connection link
  14          */
  15         protected $link;
  16
  17         /**
  18          * Database configuration
  19          */
  20         protected $db_config;
  21
  22         /**
  23          * Sets the config for the class.
  24          *
  25          * @param  array  database configuration
  26          */
  27         public function __construct($config)
  28         {
  29                 $this->db_config = $config;
  30
  31                 Kohana::log('debug', 'MSSQL Database Driver Initialized');
  32         }
  33
  34         /**
  35          * Closes the database connection.
  36          */
  37         public function __destruct()
  38         {
  39                 is_resource($this->link) and mssql_close($this->link);
  40         }
  41
  42         /**
  43          * Make the connection
  44          *
  45          * @return return connection
  46          */
  47         public function connect()
  48         {
  49                 // Check if link already exists
  50                 if (is_resource($this->link))
  51                         return $this->link;
  52
  53                 // Import the connect variables
  54                 extract($this->db_config['connection']);
  55
  56                 // Persistent connections enabled?
  57                 $connect = ($this->db_config['persistent'] == TRUE) ? 'mssql_pconnect' : 'mssql_connect';
  58
  59                 // Build the connection info
  60                 $host = isset($host) ? $host : $socket;
  61
  62                 // Windows uses a comma instead of a colon
  63                 $port = (isset($port) AND is_string($port)) ? (KOHANA_IS_WIN ? ',' : ':').$port : '';
  64
  65                 // Make the connection and select the database
  66                 if (($this->link = $connect($host.$port, $user, $pass, TRUE)) AND mssql_select_db($database, $this->link))
  67                 {
  68                         /* This is being removed so I can use it, will need to come up with a more elegant workaround in the future...
  69                          *
  70                         if ($charset = $this->db_config['character_set'])
  71                         {
  72                                 $this->set_charset($charset);
  73                         }
  74                         */
  75
  76                         // Clear password after successful connect
  77                         $this->db_config['connection']['pass'] = NULL;
  78
  79                         return $this->link;
  80                 }
  81
  82                 return FALSE;
  83         }
  84
  85         public function query($sql)
  86         {
  87                 // Only cache if it's turned on, and only cache if it's not a write statement
  88                 if ($this->db_config['cache'] AND ! preg_match('#\b(?:INSERT|UPDATE|REPLACE|SET)\b#i', $sql))
  89                 {
  90                         $hash = $this->query_hash($sql);
  91
  92                         if ( ! isset($this->query_cache[$hash]))
  93                         {
  94                                 // Set the cached object
  95                                 $this->query_cache[$hash] = new Mssql_Result(mssql_query($sql, $this->link), $this->link, $this->db_config['object'], $sql);
  96                         }
  97                         else
  98                         {
  99                                 // Rewind cached result
 100                                 $this->query_cache[$hash]->rewind();
 101                         }
 102
 103                         // Return the cached query
 104                         return $this->query_cache[$hash];
 105                 }
 106
 107                 return new Mssql_Result(mssql_query($sql, $this->link), $this->link, $this->db_config['object'], $sql);
 108         }
 109
 110         public function escape_table($table)
 111         {
 112                 if (stripos($table, ' AS ') !== FALSE)
 113                 {
 114                         // Force 'AS' to uppercase
 115                         $table = str_ireplace(' AS ', ' AS ', $table);
 116
 117                         // Runs escape_table on both sides of an AS statement
 118                         $table = array_map(array($this, __FUNCTION__), explode(' AS ', $table));
 119
 120                         // Re-create the AS statement
 121                         return implode(' AS ', $table);
 122                 }
 123                 return '['.str_replace('.', '[.]', $table).']';
 124         }
 125
 126         public function escape_column($column)
 127         {
 128                 if (!$this->db_config['escape'])
 129                         return $column;
 130
 131                 if ($column == '*')
 132                         return $column;
 133
 134                 // This matches any functions we support to SELECT.
 135                 if ( preg_match('/(avg|count|sum|max|min)\(\s*(.*)\s*\)(\s*as\s*(.+)?)?/i', $column, $matches))
 136                 {
 137                         if ( count($matches) == 3)
 138                         {
 139                                 return $matches[1].'('.$this->escape_column($matches[2]).')';
 140                         }
 141                         else if ( count($matches) == 5)
 142                         {
 143                                 return $matches[1].'('.$this->escape_column($matches[2]).') AS '.$this->escape_column($matches[2]);
 144                         }
 145                 }
 146
 147                 // This matches any modifiers we support to SELECT.
 148                 if ( ! preg_match('/\b(?:rand|all|distinct(?:row)?|high_priority|sql_(?:small_result|b(?:ig_result|uffer_result)|no_cache|ca(?:che|lc_found_rows)))\s/i', $column))
 149                 {
 150                         if (stripos($column, ' AS ') !== FALSE)
 151                         {
 152                                 // Force 'AS' to uppercase
 153                                 $column = str_ireplace(' AS ', ' AS ', $column);
 154
 155                                 // Runs escape_column on both sides of an AS statement
 156                                 $column = array_map(array($this, __FUNCTION__), explode(' AS ', $column));
 157
 158                                 // Re-create the AS statement
 159                                 return implode(' AS ', $column);
 160                         }
 161
 162                         return preg_replace('/[^.*]+/', '[$0]', $column);
 163                 }
 164
 165                 $parts = explode(' ', $column);
 166                 $column = '';
 167
 168                 for ($i = 0, $c = count($parts); $i < $c; $i++)
 169                 {
 170                         // The column is always last
 171                         if ($i == ($c - 1))
 172                         {
 173                                 $column .= preg_replace('/[^.*]+/', '[$0]', $parts[$i]);
 174                         }
 175                         else // otherwise, it's a modifier
 176                         {
 177                                 $column .= $parts[$i].' ';
 178                         }
 179                 }
 180                 return $column;
 181         }
 182
 183         /**
 184          * Limit in SQL Server 2000 only uses the keyword
 185          * 'TOP'; 2007 may have an offset keyword, but
 186          * I am unsure - for pagination style limit,offset
 187          * functionality, a fancy query needs to be built.
 188          *
 189          * @param unknown_type $limit
 190          * @return unknown
 191          */
 192         public function limit($limit, $offset=null)
 193         {
 194                 return 'TOP '.$limit;
 195         }
 196
 197         public function compile_select($database)
 198         {
 199                 $sql = ($database['distinct'] == TRUE) ? 'SELECT DISTINCT ' : 'SELECT ';
 200                 $sql .= (count($database['select']) > 0) ? implode(', ', $database['select']) : '*';
 201
 202                 if (count($database['from']) > 0)
 203                 {
 204                         // Escape the tables
 205                         $froms = array();
 206                         foreach ($database['from'] as $from)
 207                                 $froms[] = $this->escape_column($from);
 208                         $sql .= "\nFROM ";
 209                         $sql .= implode(', ', $froms);
 210                 }
 211
 212                 if (count($database['join']) > 0)
 213                 {
 214                         foreach($database['join'] AS $join)
 215                         {
 216                                 $sql .= "\n".$join['type'].'JOIN '.implode(', ', $join['tables']).' ON '.$join['conditions'];
 217                         }
 218                 }
 219
 220                 if (count($database['where']) > 0)
 221                 {
 222                         $sql .= "\nWHERE ";
 223                 }
 224
 225                 $sql .= implode("\n", $database['where']);
 226
 227                 if (count($database['groupby']) > 0)
 228                 {
 229                         $sql .= "\nGROUP BY ";
 230                         $sql .= implode(', ', $database['groupby']);
 231                 }
 232
 233                 if (count($database['having']) > 0)
 234                 {
 235                         $sql .= "\nHAVING ";
 236                         $sql .= implode("\n", $database['having']);
 237                 }
 238
 239                 if (count($database['orderby']) > 0)
 240                 {
 241                         $sql .= "\nORDER BY ";
 242                         $sql .= implode(', ', $database['orderby']);
 243                 }
 244
 245                 if (is_numeric($database['limit']))
 246                 {
 247                         $sql .= "\n";
 248                         $sql .= $this->limit($database['limit']);
 249                 }
 250
 251                 return $sql;
 252         }
 253
 254         public function escape_str($str)
 255         {
 256                 if (!$this->db_config['escape'])
 257                         return $str;
 258
 259                 is_resource($this->link) or $this->connect();
 260                 //mssql_real_escape_string($str, $this->link); <-- this function doesn't exist
 261
 262                 $characters = array('/\x00/', '/\x1a/', '/\n/', '/\r/', '/\\\/', '/\'/');
 263                 $replace    = array('\\\x00', '\\x1a', '\\n', '\\r', '\\\\', "''");
 264                 return preg_replace($characters, $replace, $str);
 265         }
 266
 267         public function list_tables()
 268         {
 269                 $sql    = 'SHOW TABLES FROM ['.$this->db_config['connection']['database'].']';
 270                 $result = $this->query($sql)->result(FALSE, MSSQL_ASSOC);
 271
 272                 $retval = array();
 273                 foreach ($result as $row)
 274                 {
 275                         $retval[] = current($row);
 276                 }
 277
 278                 return $retval;
 279         }
 280
 281         public function show_error()
 282         {
 283                 return mssql_get_last_message($this->link);
 284         }
 285
 286         public function list_fields($table)
 287         {
 288                 $result = array();
 289
 290                 foreach ($this->field_data($table) as $row)
 291                 {
 292                         // Make an associative array
 293                         $result[$row->Field] = $this->sql_type($row->Type);
 294                 }
 295
 296                 return $result;
 297         }
 298
 299         public function field_data($table)
 300         {
 301                 $query = $this->query("SELECT COLUMN_NAME AS Field, DATA_TYPE as Type  FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$this->escape_table($table)."'", $this->link);
 302
 303                 return $query->result_array(TRUE);
 304         }
 305 }
 306
 307 /**
 308  * MSSQL Result
 309  */
 310 class Mssql_Result extends Database_Result {
 311
 312         // Fetch function and return type
 313         protected $fetch_type  = 'mssql_fetch_object';
 314         protected $return_type = MSSQL_ASSOC;
 315
 316         /**
 317          * Sets up the result variables.
 318          *
 319          * @param  resource  query result
 320          * @param  resource  database link
 321          * @param  boolean   return objects or arrays
 322          * @param  string    SQL query that was run
 323          */
 324         public function __construct($result, $link, $object = TRUE, $sql)
 325         {
 326                 $this->result = $result;
 327
 328                 // If the query is a resource, it was a SELECT, SHOW, DESCRIBE, EXPLAIN query
 329                 if (is_resource($result))
 330                 {
 331                         $this->current_row = 0;
 332                         $this->total_rows  = mssql_num_rows($this->result);
 333                         $this->fetch_type = ($object === TRUE) ? 'mssql_fetch_object' : 'mssql_fetch_array';
 334                 }
 335                 elseif (is_bool($result))
 336                 {
 337                         if ($result == FALSE)
 338                         {
 339                                 // SQL error
 340                                 throw new Kohana_Database_Exception('database.error', mssql_get_last_message($link).' - '.$sql);
 341                         }
 342                         else
 343                         {
 344                                 // Its an DELETE, INSERT, REPLACE, or UPDATE querys
 345                                 $last_id          = mssql_query('SELECT @@IDENTITY AS last_id', $link);
 346                                 $result           = mssql_fetch_assoc($last_id);
 347                                 $this->insert_id  = $result['last_id'];
 348                                 $this->total_rows = mssql_rows_affected($link);
 349                         }
 350                 }
 351
 352                 // Set result type
 353                 $this->result($object);
 354
 355                 // Store the SQL
 356                 $this->sql = $sql;
 357         }
 358
 359         /**
 360          * Destruct, the cleanup crew!
 361          */
 362         public function __destruct()
 363         {
 364                 if (is_resource($this->result))
 365                 {
 366                         mssql_free_result($this->result);
 367                 }
 368         }
 369
 370         public function result($object = TRUE, $type = MSSQL_ASSOC)
 371         {
 372                 $this->fetch_type = ((bool) $object) ? 'mssql_fetch_object' : 'mssql_fetch_array';
 373
 374                 // This check has to be outside the previous statement, because we do not
 375                 // know the state of fetch_type when $object = NULL
 376                 // NOTE - The class set by $type must be defined before fetching the result,
 377                 // autoloading is disabled to save a lot of stupid overhead.
 378                 if ($this->fetch_type == 'mssql_fetch_object')
 379                 {
 380                         $this->return_type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 381                 }
 382                 else
 383                 {
 384                         $this->return_type = $type;
 385                 }
 386
 387                 return $this;
 388         }
 389
 390         public function as_array($object = NULL, $type = MSSQL_ASSOC)
 391         {
 392                 return $this->result_array($object, $type);
 393         }
 394
 395         public function result_array($object = NULL, $type = MSSQL_ASSOC)
 396         {
 397                 $rows = array();
 398
 399                 if (is_string($object))
 400                 {
 401                         $fetch = $object;
 402                 }
 403                 elseif (is_bool($object))
 404                 {
 405                         if ($object === TRUE)
 406                         {
 407                                 $fetch = 'mssql_fetch_object';
 408
 409                                 // NOTE - The class set by $type must be defined before fetching the result,
 410                                 // autoloading is disabled to save a lot of stupid overhead.
 411                                 $type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 412                         }
 413                         else
 414                         {
 415                                 $fetch = 'mssql_fetch_array';
 416                         }
 417                 }
 418                 else
 419                 {
 420                         // Use the default config values
 421                         $fetch = $this->fetch_type;
 422
 423                         if ($fetch == 'mssql_fetch_object')
 424                         {
 425                                 $type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 426                         }
 427                 }
 428
 429                 if (mssql_num_rows($this->result))
 430                 {
 431                         // Reset the pointer location to make sure things work properly
 432                         mssql_data_seek($this->result, 0);
 433
 434                         while ($row = $fetch($this->result, $type))
 435                         {
 436                                 $rows[] = $row;
 437                         }
 438                 }
 439
 440                 return isset($rows) ? $rows : array();
 441         }
 442
 443         public function list_fields()
 444         {
 445                 $field_names = array();
 446                 while ($field = mssql_fetch_field($this->result))
 447                 {
 448                         $field_names[] = $field->name;
 449                 }
 450
 451                 return $field_names;
 452         }
 453
 454         public function seek($offset)
 455         {
 456                 if ( ! $this->offsetExists($offset))
 457                         return FALSE;
 458
 459                 return mssql_data_seek($this->result, $offset);
 460         }
 461
 462 } // End mssql_Result Class