system/libraries/drivers/Database/Pdogeneric.php

   1 <?php defined('SYSPATH') OR die('No direct access allowed.');
   2
   3 require_once( dirname(__FILE__)."/merlin_get_kohana_db_field_metadata.php" );
   4
   5 /*
   6  * Class: Database_Pdogeneric_Driver
   7  *  This base class implements most of the Kohana database API for PDO.
   8  *  It's abstract, since there are some pieces that are required to end up with
   9  *  a working Kohana backend that are completely backend specific.
  10  *
  11  * Version 1.0 alpha
  12  *  author    - Doutu, updated by gregmac. Ported from sqlite-only
  13  *  to mysql/sqlite by Stephan Beal (20101228). Converted to a base class by
  14  *  Robin Sonefors.
  15  *  copyright - (c) BSD
  16  *  license   - <no>
  17  */
  18
  19 abstract class Database_Pdogeneric_Driver extends Database_Driver
  20 {
  21         // Database connection link
  22         protected $link;
  23         protected $db_config;
  24
  25         /*
  26          * Constructor: __construct
  27          *  Sets up the config for the class.
  28          *
  29          * Parameters:
  30          *  config - database configuration
  31          *
  32          */
  33         public function __construct($config)
  34         {
  35                 $this->db_config = $config;
  36         }
  37
  38         public function query($sql)
  39         {
  40                 // FIXME: add caching
  41                 try
  42                 {
  43                         $sth = $this->link->prepare($sql);
  44                         return new Pdogeneric_Result($sth, $this->link, $this->db_config['object'], $sql);
  45                 }
  46                 catch (PDOException $e)
  47                 {
  48                         throw new Kohana_Database_Exception('database.error', $e->getMessage());
  49                 }
  50         }
  51
  52         public function set_charset($charset)
  53         {
  54                 # does oracle/pgsql/whatever have ways of doing this?
  55                 throw new Kohana_Database_Exception('database.not_implemented',
  56                                                     __FUNCTION__);
  57         }
  58
  59         public function escape_table($table)
  60         {
  61                 if (!$this->db_config['escape'])
  62                         return $table;
  63                 return '`'.str_replace('.', '`.`', $table).'`';
  64         }
  65
  66         public function escape_column($column)
  67         {
  68                 if (!$this->db_config['escape'])
  69                         return $column;
  70
  71                 if (strtolower($column) == 'count(*)' OR $column == '*')
  72                         return $column;
  73
  74                 // This matches any modifiers we support to SELECT.
  75                 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)) {
  76                         if (stripos($column, ' AS ') !== FALSE) {
  77                                 // Force 'AS' to uppercase
  78                                 $column = str_ireplace(' AS ', ' AS ', $column);
  79
  80                                 // Runs escape_column on both sides of an AS statement
  81                                 $column = array_map(array($this, __FUNCTION__), explode(' AS ', $column));
  82
  83                                 // Re-create the AS statement
  84                                 return implode(' AS ', $column);
  85                         }
  86                         return preg_replace('/[^.*]+/', '`$0`', $column);
  87                 }
  88
  89                 $parts = explode(' ', $column);
  90                 $column = '';
  91
  92                 for ($i = 0, $c = count($parts); $i < $c; $i++) {
  93                         // The column is always last
  94                         if ($i == ($c - 1)) {
  95                                 $column .= preg_replace('/[^.*]+/', '`$0`', $parts[$i]);
  96                         } else {    // otherwise, it's a modifier
  97                                 $column .= $parts[$i].' ';
  98                         }
  99                 }
 100                 return $column;
 101         }
 102
 103         // This *can't* be implemented properly for oracle. Stupid oracle.
 104         public function limit($limit, $offset = 0) {}
 105
 106         public function compile_select($database)
 107         {
 108                 $sql = ($database['distinct'] == TRUE) ? 'SELECT DISTINCT ' : 'SELECT ';
 109                 $sql .= (count($database['select']) > 0) ? implode(', ', $database['select']) : '*';
 110
 111                 if (count($database['from']) > 0) {
 112                         $sql .= "\nFROM ";
 113                         $sql .= implode(', ', $database['from']);
 114                 }
 115
 116                 if (count($database['join']) > 0) {
 117                         foreach($database['join'] AS $join) {
 118                                 $sql .= "\n".$join['type'].'JOIN '.implode(', ', $join['tables']).' ON '.$join['conditions'];
 119                         }
 120                 }
 121
 122                 if (count($database['where']) > 0) {
 123                         $sql .= "\nWHERE ";
 124                 }
 125
 126                 $sql .= implode("\n", $database['where']);
 127
 128                 if (count($database['groupby']) > 0) {
 129                         $sql .= "\nGROUP BY ";
 130                         $sql .= implode(', ', $database['groupby']);
 131                 }
 132
 133                 if (count($database['having']) > 0) {
 134                         $sql .= "\nHAVING ";
 135                         $sql .= implode("\n", $database['having']);
 136                 }
 137
 138                 if (count($database['orderby']) > 0) {
 139                         $sql .= "\nORDER BY ";
 140                         $sql .= implode(', ', $database['orderby']);
 141                 }
 142
 143                 if (is_numeric($database['limit'])) {
 144                         $sql .= "\n";
 145                         $sql = $this->limit($sql, $database['limit'], $database['offset']);
 146                 }
 147
 148                 return $sql;
 149         }
 150
 151         public function list_tables(Database $db)
 152         {
 153                 throw new Kohana_Database_Exception ('database.not_implemented', __FUNCTION__);
 154         }
 155
 156         public function show_error()
 157         {
 158                 $err = $this->link->errorInfo();
 159                 return isset($err[2]) ? $err[2] : 'Unknown error!';
 160         }
 161
 162         public function list_fields($table)
 163         {
 164                 static $tables = array();
 165
 166                 if (empty($tables[$table]))
 167                 {
 168                         foreach ($this->field_data($table) as $row)
 169                         {
 170                                 // Make an associative array
 171                                 $tables[$table][$row->Field] = $this->sql_type($row->Type);
 172
 173                                 if ($row->Key === 'PRI' AND $row->Extra === 'auto_increment') {
 174                                         // For sequenced (AUTO_INCREMENT) tables
 175                                         $tables[$table][$row->Field]['sequenced'] = TRUE;
 176                                 }
 177
 178                                 if ($row->Null === 'YES') {
 179                                         // Set NULL status
 180                                         $tables[$table][$row->Field]['null'] = TRUE;
 181                                 }
 182                         }
 183                 }
 184
 185                 if (!isset($tables[$table]))
 186                         throw new Kohana_Database_Exception('database.table_not_found', $table);
 187
 188                 return $tables[$table];
 189         }
 190
 191         public function field_data($table)
 192         {
 193                 /* The API docs don't specify what exactly i should be
 194                  returning here, so i just implemented a clone of the
 195                  MySQL driver's functionality. */
 196
 197                 static $columns = array();
 198                 if (!empty($columns[$table])) {
 199                         return $columns[$table];
 200                 }
 201
 202                 # UGLY FUGLY HACK: return pre-generated field data. We
 203                 # only need this for Oracle, but since we generate this
 204                 # data from MySQL, we can use it for the MySQL back-end
 205                 # as well (and save ourselves a few queries to find the
 206                 # information server-side).
 207                 $columns = merlin_get_kohana_db_field_metadata();
 208                 return $columns[$table];
 209
 210                 if ($this->isMysql()) {
 211                         $sql = 'SHOW COLUMNS FROM '.$this->escape_table($table);
 212                         $res = $this->query($sql);
 213                         $cols = array();
 214
 215                         foreach($res->result(TRUE) as $row) {
 216                                 $cols[] = $row;
 217                         }
 218                         unset($res);
 219                         return $columns[$table] = $cols;
 220                 }
 221                 else if ($this->isSqlite()) {
 222                         $sql = 'PRAGMA table_info('.$this->escape_table($table).')';
 223                         $res = $this->link->query($sql);
 224                         $cols = array();
 225                         foreach ($res as $row)
 226                         {
 227                                 $obj = new stdClass();
 228                                 /* Reminder:
 229                                  sqlite> .headers on
 230                                  sqlite> PRAGMA table_info(t1);
 231                                  cid|name|type|notnull|dflt_value|pk
 232                                  0|i|int|0||0
 233                                  1|s|varchar(32)|0||0
 234                                  */
 235                                 $obj->Field = $row[1];
 236                                 $obj->Type = $row[2];
 237                                 $obj->Null = $row[3] ? 'NO' : 'YES';
 238                                 $obj->Default = $row[4];
 239                                 $obj->Key = $row[5] ? 'PRI' : NULL;
 240                                 $obj->Extra = NULL;
 241                                 $cols[] = $obj;
 242                         }
 243                         unset($res);
 244                         return $columns[$table] = $cols;
 245                 }
 246                 else {
 247                         throw new Kohana_Database_Exception
 248                                 ('database.not_implemented', __FUNCTION__);
 249                 }
 250         }
 251
 252         /**
 253          * Version number query string
 254          *
 255          * @access      public
 256          * @return      string
 257          */
 258         function version()
 259         {
 260                 return $this->link->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
 261         }
 262
 263         function stmt_prepare($sql = '')
 264         {
 265                 is_object($this->link) or $this->connect();
 266                 return new Kohana_Pdogeneric_Statement($sql, $this->link);
 267         }
 268 } // End Database_Pdogeneric_Driver Class
 269
 270 /**
 271  * Kohana's support for prepared statements seems to be less-than-well defined.
 272  * This tries to stick to what Mysqli does (which is also what Pgsql does).
 273  *
 274  * Because I'm lazy, there's no support for binding output parameters, only
 275  * input.
 276  */
 277 class Kohana_Pdogeneric_Statement {
 278         protected $link = null;
 279         protected $stmt;
 280         protected $params = array();
 281
 282         public function __construct($sql, $link)
 283         {
 284                 $this->link = $link;
 285                 $this->stmt = $this->link->prepare($sql);
 286         }
 287
 288         /**
 289          * The first param is for a "type hint string" used in other drivers
 290          * ("si" means "a string and an int"). We don't give a crap about that.
 291          */
 292         public function bind_params($unused, $params)
 293         {
 294                 $this->params = $params;
 295         }
 296
 297         public function execute()
 298         {
 299                 $this->stmt->execute($this->params);
 300                 return $this->stmt;
 301         }
 302 }
 303
 304 /*
 305  * PDO Result
 306  */
 307 class Pdogeneric_Result extends Database_Result {
 308
 309         // Data fetching types
 310         protected $fetch_type  = PDO::FETCH_OBJ;
 311         protected $return_type = PDO::FETCH_ASSOC;
 312
 313         /**
 314          * Sets up the result variables.
 315          *
 316          * @param  resource  query result
 317          * @param  resource  database link
 318          * @param  boolean   return objects or arrays
 319          * @param  string    SQL query that was run
 320          */
 321         public function __construct($result, $link, $object = TRUE, $sql)
 322         {
 323                 if (is_object($result) OR $result = $link->prepare($sql)) {
 324                         // run the query
 325                         try {
 326                                 $result->execute();
 327                         } catch (PDOException $e) {
 328                                 throw new Kohana_Database_Exception('database.error', $e->getMessage());
 329                         }
 330
 331                         if (preg_match('/^\s*(SHOW|DESCRIBE|SELECT|PRAGMA|EXPLAIN)/i', $sql)) {
 332                                 $this->result = $result;
 333                                 $this->current_row = 0;
 334
 335                                 $this->total_rows = $this->pdo_row_count();
 336
 337                                 $this->fetch_type = ($object === TRUE) ? PDO::FETCH_OBJ : PDO::FETCH_ASSOC;
 338                         } elseif (preg_match('/^\s*(DELETE|INSERT|UPDATE)/i', $sql)) {
 339                                 # completely broken, but I don't care
 340                                 $this->insert_id  = 0;
 341                         }
 342                 } else {
 343                         // SQL error
 344                         $err = $link->errorInfo();
 345                         throw new Kohana_Database_Exception
 346                                 ('database.error', $err[2].' - SQL=['.$sql.']');
 347                 }
 348
 349                 // Set result type
 350                 $this->result($object);
 351
 352                 // Store the SQL
 353                 $this->sql = $sql;
 354         }
 355
 356         protected function pdo_row_count()
 357         {
 358                 $count = 0;
 359                 while ($this->result->fetch()) {
 360                         $count++;
 361                 }
 362
 363                 // The query must be re-fetched now.
 364                 $this->result->execute();
 365                 return $count;
 366         }
 367
 368         /*
 369          * Destructor: __destruct
 370          *  Magic __destruct function, frees the result.
 371          */
 372         public function __destruct()
 373         {
 374                 if (is_object($this->result)) {
 375                         $this->result->closeCursor();
 376                         $this->result = NULL;
 377                 }
 378         }
 379
 380         public function result($object = TRUE, $type = PDO::FETCH_BOTH)
 381         {
 382                 $this->fetch_type = ((bool) $object) ? PDO::FETCH_OBJ : PDO::FETCH_BOTH;
 383
 384                 if ($this->fetch_type == PDO::FETCH_OBJ) {
 385                         $this->return_type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 386                 } else {
 387                         $this->return_type = $type;
 388                 }
 389                 return $this;
 390         }
 391
 392         public function as_array($object = NULL, $type = PDO::FETCH_ASSOC)
 393         {
 394                 return $this->result_array($object, $type);
 395         }
 396
 397         public function result_array($object = NULL, $type = PDO::FETCH_ASSOC)
 398         {
 399                 $rows = array();
 400
 401                 if (is_string($object)) {
 402                         $fetch = $object;
 403                 } elseif (is_bool($object)) {
 404                         if ($object === TRUE) {
 405                                 $fetch = PDO::FETCH_OBJ;
 406
 407                                 // NOTE - The class set by $type must be defined before fetching the result,
 408                                 // autoloading is disabled to save a lot of stupid overhead.
 409                                 $type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 410                         } else {
 411                                 $fetch = PDO::FETCH_OBJ;
 412                         }
 413                 } else {
 414                         // Use the default config values
 415                         $fetch = $this->fetch_type;
 416
 417                         if ($fetch == PDO::FETCH_OBJ) {
 418                                 $type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 419                         }
 420                 }
 421
 422                 try {
 423                         while ($row = $this->result->fetch($fetch)) {
 424                                 $rows[] = $row;
 425                         }
 426                 } catch(PDOException $e) {
 427                         throw new Kohana_Database_Exception('database.error', $e->getMessage());
 428                         return FALSE;
 429                 }
 430                 return $rows;
 431         }
 432
 433         public function list_fields()
 434         {
 435                 $field_names = array();
 436                 for ($i = 0, $max = $this->result->columnCount(); $i < $max; $i++) {
 437                         $info = $this->result->getColumnMeta($i);
 438                         $field_names[] = $info['name'];
 439                 }
 440                 return $field_names;
 441         }
 442
 443         public function seek($offset)
 444         {
 445                 # To request a scrollable cursor for your PDOStatement object,
 446                 # you must set the PDO::ATTR_CURSOR attribute to PDO::CURSOR_SCROLL
 447                 # when you prepare the statement.
 448                 Kohana::log('error', get_class($this).' does not support scrollable cursors, '.__FUNCTION__.' call ignored');
 449
 450                 return FALSE;
 451         }
 452
 453         public function offsetGet($offset)
 454         {
 455                 try {
 456                         return $this->result->fetch($this->fetch_type, PDO::FETCH_ORI_ABS, $offset);
 457                 } catch(PDOException $e) {
 458                         throw new Kohana_Database_Exception('database.error', $e->getMessage());
 459                 }
 460         }
 461
 462         public function rewind()
 463         {
 464                 # Same problem that seek() has, see above.
 465                 return $this->seek(0);
 466         }
 467
 468 } // End PdoSqlite_Result Class