system/libraries/drivers/Database/Pdosqlite.php

   1 <?php defined('SYSPATH') OR die('No direct access allowed.');
   2 /*
   3  * Class: Database_PdoSqlite_Driver
   4  *  Provides specific database items for Sqlite.
   5  *
   6  * Connection string should be, eg: "pdosqlite://path/to/database.db"
   7  *
   8  * Version 1.0 alpha
   9  *  author    - Doutu, updated by gregmac
  10  *  copyright - (c) BSD
  11  *  license   - <no>
  12  */
  13
  14 class Database_Pdosqlite_Driver extends Database_Driver {
  15
  16         // Database connection link
  17         protected $link;
  18         protected $db_config;
  19
  20         /*
  21          * Constructor: __construct
  22          *  Sets up the config for the class.
  23          *
  24          * Parameters:
  25          *  config - database configuration
  26          *
  27          */
  28         public function __construct($config)
  29         {
  30                 $this->db_config = $config;
  31
  32                 Kohana::log('debug', 'PDO:Sqlite Database Driver Initialized');
  33         }
  34
  35         public function connect()
  36         {
  37                 // Import the connect variables
  38                 extract($this->db_config['connection']);
  39
  40                 try
  41                 {
  42                         $this->link = new PDO('sqlite:'.$socket.$database, $user, $pass,
  43                                 array(PDO::ATTR_PERSISTENT => $this->db_config['persistent']));
  44
  45                         $this->link->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
  46                         $this->link->query('PRAGMA count_changes=1;');
  47
  48                         if ($charset = $this->db_config['character_set'])
  49                         {
  50                                 $this->set_charset($charset);
  51                         }
  52                 }
  53                 catch (PDOException $e)
  54                 {
  55                         throw new Kohana_Database_Exception('database.error', $e->getMessage());
  56                 }
  57
  58                 // Clear password after successful connect
  59                 $this->db_config['connection']['pass'] = NULL;
  60
  61                 return $this->link;
  62         }
  63
  64         public function query($sql)
  65         {
  66                 try
  67                 {
  68                         $sth = $this->link->prepare($sql);
  69                 }
  70                 catch (PDOException $e)
  71                 {
  72                         throw new Kohana_Database_Exception('database.error', $e->getMessage());
  73                 }
  74                 return new Pdosqlite_Result($sth, $this->link, $this->db_config['object'], $sql);
  75         }
  76
  77         public function set_charset($charset)
  78         {
  79                 $this->link->query('PRAGMA encoding = '.$this->escape_str($charset));
  80         }
  81
  82         public function escape_table($table)
  83         {
  84                 if ( ! $this->db_config['escape'])
  85                         return $table;
  86
  87                 return '`'.str_replace('.', '`.`', $table).'`';
  88         }
  89
  90         public function escape_column($column)
  91         {
  92                 if ( ! $this->db_config['escape'])
  93                         return $column;
  94
  95                 if (strtolower($column) == 'count(*)' OR $column == '*')
  96                         return $column;
  97
  98                 // This matches any modifiers we support to SELECT.
  99                 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))
 100                 {
 101                         if (stripos($column, ' AS ') !== FALSE)
 102                         {
 103                                 // Force 'AS' to uppercase
 104                                 $column = str_ireplace(' AS ', ' AS ', $column);
 105
 106                                 // Runs escape_column on both sides of an AS statement
 107                                 $column = array_map(array($this, __FUNCTION__), explode(' AS ', $column));
 108
 109                                 // Re-create the AS statement
 110                                 return implode(' AS ', $column);
 111                         }
 112
 113                         return preg_replace('/[^.*]+/', '`$0`', $column);
 114                 }
 115
 116                 $parts = explode(' ', $column);
 117                 $column = '';
 118
 119                 for ($i = 0, $c = count($parts); $i < $c; $i++)
 120                 {
 121                         // The column is always last
 122                         if ($i == ($c - 1))
 123                         {
 124                                 $column .= preg_replace('/[^.*]+/', '`$0`', $parts[$i]);
 125                         }
 126                         else // otherwise, it's a modifier
 127                         {
 128                                 $column .= $parts[$i].' ';
 129                         }
 130                 }
 131                 return $column;
 132         }
 133
 134         public function limit($limit, $offset = 0)
 135         {
 136                 return 'LIMIT '.$offset.', '.$limit;
 137         }
 138
 139         public function compile_select($database)
 140         {
 141                 $sql = ($database['distinct'] == TRUE) ? 'SELECT DISTINCT ' : 'SELECT ';
 142                 $sql .= (count($database['select']) > 0) ? implode(', ', $database['select']) : '*';
 143
 144                 if (count($database['from']) > 0)
 145                 {
 146                         $sql .= "\nFROM ";
 147                         $sql .= implode(', ', $database['from']);
 148                 }
 149
 150                 if (count($database['join']) > 0)
 151                 {
 152                         foreach($database['join'] AS $join)
 153                         {
 154                                 $sql .= "\n".$join['type'].'JOIN '.implode(', ', $join['tables']).' ON '.$join['conditions'];
 155                         }
 156                 }
 157
 158                 if (count($database['where']) > 0)
 159                 {
 160                         $sql .= "\nWHERE ";
 161                 }
 162
 163                 $sql .= implode("\n", $database['where']);
 164
 165                 if (count($database['groupby']) > 0)
 166                 {
 167                         $sql .= "\nGROUP BY ";
 168                         $sql .= implode(', ', $database['groupby']);
 169                 }
 170
 171                 if (count($database['having']) > 0)
 172                 {
 173                         $sql .= "\nHAVING ";
 174                         $sql .= implode("\n", $database['having']);
 175                 }
 176
 177                 if (count($database['orderby']) > 0)
 178                 {
 179                         $sql .= "\nORDER BY ";
 180                         $sql .= implode(', ', $database['orderby']);
 181                 }
 182
 183                 if (is_numeric($database['limit']))
 184                 {
 185                         $sql .= "\n";
 186                         $sql .= $this->limit($database['limit'], $database['offset']);
 187                 }
 188
 189                 return $sql;
 190         }
 191
 192         public function escape_str($str)
 193         {
 194                 if ( ! $this->db_config['escape'])
 195                         return $str;
 196
 197                 if (function_exists('sqlite_escape_string'))
 198                 {
 199                         $res = sqlite_escape_string($str);
 200                 }
 201                 else
 202                 {
 203                         $res = str_replace("'", "''", $str);
 204                 }
 205                 return $res;
 206         }
 207
 208         public function list_tables(Database $db)
 209         {
 210                 $sql = "SELECT `name` FROM `sqlite_master` WHERE `type`='table' ORDER BY `name`;";
 211                 try
 212                 {
 213                         $result = $db->query($sql)->result(FALSE, PDO::FETCH_ASSOC);
 214                         $tables = array();
 215                         foreach ($result as $row)
 216                         {
 217                                 $tables[] = current($row);
 218                         }
 219                 }
 220                 catch (PDOException $e)
 221                 {
 222                         throw new Kohana_Database_Exception('database.error', $e->getMessage());
 223                 }
 224                 return $tables;
 225         }
 226
 227         public function show_error()
 228         {
 229                 $err = $this->link->errorInfo();
 230                 return isset($err[2]) ? $err[2] : 'Unknown error!';
 231         }
 232
 233         public function list_fields($table, $query = FALSE)
 234         {
 235                 static $tables;
 236                 if (is_object($query))
 237                 {
 238                         if (empty($tables[$table]))
 239                         {
 240                                 $tables[$table] = array();
 241
 242                                 foreach ($query->result() as $row)
 243                                 {
 244                                         $tables[$table][] = $row->name;
 245                                 }
 246                         }
 247
 248                         return $tables[$table];
 249                 }
 250                 else
 251                 {
 252                         $result = $this->link->query( 'PRAGMA table_info('.$this->escape_table($table).')' );
 253
 254                         foreach ($result as $row)
 255                         {
 256                                 $tables[$table][$row['name']] = $this->sql_type($row['type']);
 257                         }
 258
 259                         return $tables[$table];
 260                 }
 261         }
 262
 263         public function field_data($table)
 264         {
 265                 Kohana::log('error', 'This method is under developing');
 266         }
 267         /**
 268          * Version number query string
 269          *
 270          * @access      public
 271          * @return      string
 272          */
 273         function version()
 274         {
 275                 return $this->link->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
 276         }
 277
 278 } // End Database_PdoSqlite_Driver Class
 279
 280 /*
 281  * PDO-sqlite Result
 282  */
 283 class Pdosqlite_Result extends Database_Result {
 284
 285         // Data fetching types
 286         protected $fetch_type  = PDO::FETCH_OBJ;
 287         protected $return_type = PDO::FETCH_ASSOC;
 288
 289         /**
 290          * Sets up the result variables.
 291          *
 292          * @param  resource  query result
 293          * @param  resource  database link
 294          * @param  boolean   return objects or arrays
 295          * @param  string    SQL query that was run
 296          */
 297         public function __construct($result, $link, $object = TRUE, $sql)
 298         {
 299                 if (is_object($result) OR $result = $link->prepare($sql))
 300                 {
 301                         // run the query
 302                         try
 303                         {
 304                                 $result->execute();
 305                         }
 306                         catch (PDOException $e)
 307                         {
 308                                 throw new Kohana_Database_Exception('database.error', $e->getMessage());
 309                         }
 310
 311                         if (preg_match('/^SELECT|PRAGMA|EXPLAIN/i', $sql))
 312                         {
 313                                 $this->result = $result;
 314                                 $this->current_row = 0;
 315
 316                                 $this->total_rows = $this->sqlite_row_count();
 317
 318                                 $this->fetch_type = ($object === TRUE) ? PDO::FETCH_OBJ : PDO::FETCH_ASSOC;
 319                         }
 320                         elseif (preg_match('/^DELETE|INSERT|UPDATE/i', $sql))
 321                         {
 322                                 $this->insert_id  = $link->lastInsertId();
 323                         }
 324                 }
 325                 else
 326                 {
 327                         // SQL error
 328                         throw new Kohana_Database_Exception('database.error', $link->errorInfo().' - '.$sql);
 329                 }
 330
 331                 // Set result type
 332                 $this->result($object);
 333
 334                 // Store the SQL
 335                 $this->sql = $sql;
 336         }
 337
 338         private function sqlite_row_count()
 339         {
 340                 $count = 0;
 341                 while ($this->result->fetch())
 342                 {
 343                         $count++;
 344                 }
 345
 346                 // The query must be re-fetched now.
 347                 $this->result->execute();
 348
 349                 return $count;
 350         }
 351
 352         /*
 353          * Destructor: __destruct
 354          *  Magic __destruct function, frees the result.
 355          */
 356         public function __destruct()
 357         {
 358                 if (is_object($this->result))
 359                 {
 360                         $this->result->closeCursor();
 361                         $this->result = NULL;
 362                 }
 363         }
 364
 365         public function result($object = TRUE, $type = PDO::FETCH_BOTH)
 366         {
 367                 $this->fetch_type = (bool) $object ? PDO::FETCH_OBJ : PDO::FETCH_BOTH;
 368
 369                 if ($this->fetch_type == PDO::FETCH_OBJ)
 370                 {
 371                         $this->return_type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 372                 }
 373                 else
 374                 {
 375                         $this->return_type = $type;
 376                 }
 377
 378                 return $this;
 379         }
 380
 381         public function as_array($object = NULL, $type = PDO::FETCH_ASSOC)
 382         {
 383                 return $this->result_array($object, $type);
 384         }
 385
 386         public function result_array($object = NULL, $type = PDO::FETCH_ASSOC)
 387         {
 388                 $rows = array();
 389
 390                 if (is_string($object))
 391                 {
 392                         $fetch = $object;
 393                 }
 394                 elseif (is_bool($object))
 395                 {
 396                         if ($object === TRUE)
 397                         {
 398                                 $fetch = PDO::FETCH_OBJ;
 399
 400                                 // NOTE - The class set by $type must be defined before fetching the result,
 401                                 // autoloading is disabled to save a lot of stupid overhead.
 402                                 $type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 403                         }
 404                         else
 405                         {
 406                                 $fetch = PDO::FETCH_OBJ;
 407                         }
 408                 }
 409                 else
 410                 {
 411                         // Use the default config values
 412                         $fetch = $this->fetch_type;
 413
 414                         if ($fetch == PDO::FETCH_OBJ)
 415                         {
 416                                 $type = (is_string($type) AND Kohana::auto_load($type)) ? $type : 'stdClass';
 417                         }
 418                 }
 419                 try
 420                 {
 421                         while ($row = $this->result->fetch($fetch))
 422                         {
 423                                 $rows[] = $row;
 424                         }
 425                 }
 426                 catch(PDOException $e)
 427                 {
 428                         throw new Kohana_Database_Exception('database.error', $e->getMessage());
 429                         return FALSE;
 430                 }
 431                 return $rows;
 432         }
 433
 434         public function list_fields()
 435         {
 436                 $field_names = array();
 437                 for ($i = 0, $max = $this->result->columnCount(); $i < $max; $i++)
 438                 {
 439                         $info = $this->result->getColumnMeta($i);
 440                         $field_names[] = $info['name'];
 441                 }
 442                 return $field_names;
 443         }
 444
 445         public function seek($offset)
 446         {
 447                 // To request a scrollable cursor for your PDOStatement object, you must
 448                 // set the PDO::ATTR_CURSOR attribute to PDO::CURSOR_SCROLL when you
 449                 // prepare the statement.
 450                 Kohana::log('error', get_class($this).' does not support scrollable cursors, '.__FUNCTION__.' call ignored');
 451
 452                 return FALSE;
 453         }
 454
 455         public function offsetGet($offset)
 456         {
 457                 try
 458                 {
 459                         return $this->result->fetch($this->fetch_type, PDO::FETCH_ORI_ABS, $offset);
 460                 }
 461                 catch(PDOException $e)
 462                 {
 463                         throw new Kohana_Database_Exception('database.error', $e->getMessage());
 464                 }
 465         }
 466
 467         public function rewind()
 468         {
 469                 // Same problem that seek() has, see above.
 470                 return $this->seek(0);
 471         }
 472
 473 } // End PdoSqlite_Result Class