video/system/database/drivers/sqlite/sqlite_driver.php

   1 <?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
   2 /**
   3  * CodeIgniter
   4  *
   5  * An open source application development framework for PHP 5.1.6 or newer
   6  *
   7  * @package             CodeIgniter
   8  * @author              ExpressionEngine Dev Team
   9  * @copyright   Copyright (c) 2008 - 2011, EllisLab, Inc.
  10  * @license             http://codeigniter.com/user_guide/license.html
  11  * @link                http://codeigniter.com
  12  * @since               Version 1.0
  13  * @filesource
  14  */
  15
  16 // ------------------------------------------------------------------------
  17
  18
  19
  20 /**
  21  * SQLite Database Adapter Class
  22  *
  23  * Note: _DB is an extender class that the app controller
  24  * creates dynamically based on whether the active record
  25  * class is being used or not.
  26  *
  27  * @package             CodeIgniter
  28  * @subpackage  Drivers
  29  * @category    Database
  30  * @author              ExpressionEngine Dev Team
  31  * @link                http://codeigniter.com/user_guide/database/
  32  */
  33 class CI_DB_sqlite_driver extends CI_DB {
  34
  35         var $dbdriver = 'sqlite';
  36
  37         // The character used to escape with - not needed for SQLite
  38         var $_escape_char = '';
  39
  40         // clause and character used for LIKE escape sequences
  41         var $_like_escape_str = " ESCAPE '%s' ";
  42         var $_like_escape_chr = '!';
  43
  44         /**
  45          * The syntax to count rows is slightly different across different
  46          * database engines, so this string appears in each driver and is
  47          * used for the count_all() and count_all_results() functions.
  48          */
  49         var $_count_string = "SELECT COUNT(*) AS ";
  50         var $_random_keyword = ' Random()'; // database specific random keyword
  51
  52         /**
  53          * Non-persistent database connection
  54          *
  55          * @access      private called by the base class
  56          * @return      resource
  57          */
  58         function db_connect()
  59         {
  60                 if ( ! $conn_id = @sqlite_open($this->database, FILE_WRITE_MODE, $error))
  61                 {
  62                         log_message('error', $error);
  63
  64                         if ($this->db_debug)
  65                         {
  66                                 $this->display_error($error, '', TRUE);
  67                         }
  68
  69                         return FALSE;
  70                 }
  71
  72                 return $conn_id;
  73         }
  74
  75         // --------------------------------------------------------------------
  76
  77         /**
  78          * Persistent database connection
  79          *
  80          * @access      private called by the base class
  81          * @return      resource
  82          */
  83         function db_pconnect()
  84         {
  85                 if ( ! $conn_id = @sqlite_popen($this->database, FILE_WRITE_MODE, $error))
  86                 {
  87                         log_message('error', $error);
  88
  89                         if ($this->db_debug)
  90                         {
  91                                 $this->display_error($error, '', TRUE);
  92                         }
  93
  94                         return FALSE;
  95                 }
  96
  97                 return $conn_id;
  98         }
  99
 100         // --------------------------------------------------------------------
 101
 102         /**
 103          * Reconnect
 104          *
 105          * Keep / reestablish the db connection if no queries have been
 106          * sent for a length of time exceeding the server's idle timeout
 107          *
 108          * @access      public
 109          * @return      void
 110          */
 111         function reconnect()
 112         {
 113                 // not implemented in SQLite
 114         }
 115
 116         // --------------------------------------------------------------------
 117
 118         /**
 119          * Select the database
 120          *
 121          * @access      private called by the base class
 122          * @return      resource
 123          */
 124         function db_select()
 125         {
 126                 return TRUE;
 127         }
 128
 129         // --------------------------------------------------------------------
 130
 131         /**
 132          * Set client character set
 133          *
 134          * @access      public
 135          * @param       string
 136          * @param       string
 137          * @return      resource
 138          */
 139         function db_set_charset($charset, $collation)
 140         {
 141                 // @todo - add support if needed
 142                 return TRUE;
 143         }
 144
 145         // --------------------------------------------------------------------
 146
 147         /**
 148          * Version number query string
 149          *
 150          * @access      public
 151          * @return      string
 152          */
 153         function _version()
 154         {
 155                 return sqlite_libversion();
 156         }
 157
 158         // --------------------------------------------------------------------
 159
 160         /**
 161          * Execute the query
 162          *
 163          * @access      private called by the base class
 164          * @param       string  an SQL query
 165          * @return      resource
 166          */
 167         function _execute($sql)
 168         {
 169                 $sql = $this->_prep_query($sql);
 170                 return @sqlite_query($this->conn_id, $sql);
 171         }
 172
 173         // --------------------------------------------------------------------
 174
 175         /**
 176          * Prep the query
 177          *
 178          * If needed, each database adapter can prep the query string
 179          *
 180          * @access      private called by execute()
 181          * @param       string  an SQL query
 182          * @return      string
 183          */
 184         function _prep_query($sql)
 185         {
 186                 return $sql;
 187         }
 188
 189         // --------------------------------------------------------------------
 190
 191         /**
 192          * Begin Transaction
 193          *
 194          * @access      public
 195          * @return      bool
 196          */
 197         function trans_begin($test_mode = FALSE)
 198         {
 199                 if ( ! $this->trans_enabled)
 200                 {
 201                         return TRUE;
 202                 }
 203
 204                 // When transactions are nested we only begin/commit/rollback the outermost ones
 205                 if ($this->_trans_depth > 0)
 206                 {
 207                         return TRUE;
 208                 }
 209
 210                 // Reset the transaction failure flag.
 211                 // If the $test_mode flag is set to TRUE transactions will be rolled back
 212                 // even if the queries produce a successful result.
 213                 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
 214
 215                 $this->simple_query('BEGIN TRANSACTION');
 216                 return TRUE;
 217         }
 218
 219         // --------------------------------------------------------------------
 220
 221         /**
 222          * Commit Transaction
 223          *
 224          * @access      public
 225          * @return      bool
 226          */
 227         function trans_commit()
 228         {
 229                 if ( ! $this->trans_enabled)
 230                 {
 231                         return TRUE;
 232                 }
 233
 234                 // When transactions are nested we only begin/commit/rollback the outermost ones
 235                 if ($this->_trans_depth > 0)
 236                 {
 237                         return TRUE;
 238                 }
 239
 240                 $this->simple_query('COMMIT');
 241                 return TRUE;
 242         }
 243
 244         // --------------------------------------------------------------------
 245
 246         /**
 247          * Rollback Transaction
 248          *
 249          * @access      public
 250          * @return      bool
 251          */
 252         function trans_rollback()
 253         {
 254                 if ( ! $this->trans_enabled)
 255                 {
 256                         return TRUE;
 257                 }
 258
 259                 // When transactions are nested we only begin/commit/rollback the outermost ones
 260                 if ($this->_trans_depth > 0)
 261                 {
 262                         return TRUE;
 263                 }
 264
 265                 $this->simple_query('ROLLBACK');
 266                 return TRUE;
 267         }
 268
 269         // --------------------------------------------------------------------
 270
 271         /**
 272          * Escape String
 273          *
 274          * @access      public
 275          * @param       string
 276          * @param       bool    whether or not the string will be used in a LIKE condition
 277          * @return      string
 278          */
 279         function escape_str($str, $like = FALSE)
 280         {
 281                 if (is_array($str))
 282                 {
 283                         foreach ($str as $key => $val)
 284                         {
 285                                 $str[$key] = $this->escape_str($val, $like);
 286                         }
 287
 288                         return $str;
 289                 }
 290
 291                 $str = sqlite_escape_string($str);
 292
 293                 // escape LIKE condition wildcards
 294                 if ($like === TRUE)
 295                 {
 296                         $str = str_replace(     array('%', '_', $this->_like_escape_chr),
 297                                                                 array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
 298                                                                 $str);
 299                 }
 300
 301                 return $str;
 302         }
 303
 304         // --------------------------------------------------------------------
 305
 306         /**
 307          * Affected Rows
 308          *
 309          * @access      public
 310          * @return      integer
 311          */
 312         function affected_rows()
 313         {
 314                 return sqlite_changes($this->conn_id);
 315         }
 316
 317         // --------------------------------------------------------------------
 318
 319         /**
 320          * Insert ID
 321          *
 322          * @access      public
 323          * @return      integer
 324          */
 325         function insert_id()
 326         {
 327                 return @sqlite_last_insert_rowid($this->conn_id);
 328         }
 329
 330         // --------------------------------------------------------------------
 331
 332         /**
 333          * "Count All" query
 334          *
 335          * Generates a platform-specific query string that counts all records in
 336          * the specified database
 337          *
 338          * @access      public
 339          * @param       string
 340          * @return      string
 341          */
 342         function count_all($table = '')
 343         {
 344                 if ($table == '')
 345                 {
 346                         return 0;
 347                 }
 348
 349                 $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
 350
 351                 if ($query->num_rows() == 0)
 352                 {
 353                         return 0;
 354                 }
 355
 356                 $row = $query->row();
 357                 $this->_reset_select();
 358                 return (int) $row->numrows;
 359         }
 360
 361         // --------------------------------------------------------------------
 362
 363         /**
 364          * List table query
 365          *
 366          * Generates a platform-specific query string so that the table names can be fetched
 367          *
 368          * @access      private
 369          * @param       boolean
 370          * @return      string
 371          */
 372         function _list_tables($prefix_limit = FALSE)
 373         {
 374                 $sql = "SELECT name from sqlite_master WHERE type='table'";
 375
 376                 if ($prefix_limit !== FALSE AND $this->dbprefix != '')
 377                 {
 378                         $sql .= " AND 'name' LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr);
 379                 }
 380                 return $sql;
 381         }
 382
 383         // --------------------------------------------------------------------
 384
 385         /**
 386          * Show column query
 387          *
 388          * Generates a platform-specific query string so that the column names can be fetched
 389          *
 390          * @access      public
 391          * @param       string  the table name
 392          * @return      string
 393          */
 394         function _list_columns($table = '')
 395         {
 396                 // Not supported
 397                 return FALSE;
 398         }
 399
 400         // --------------------------------------------------------------------
 401
 402         /**
 403          * Field data query
 404          *
 405          * Generates a platform-specific query so that the column data can be retrieved
 406          *
 407          * @access      public
 408          * @param       string  the table name
 409          * @return      object
 410          */
 411         function _field_data($table)
 412         {
 413                 return "SELECT * FROM ".$table." LIMIT 1";
 414         }
 415
 416         // --------------------------------------------------------------------
 417
 418         /**
 419          * The error message string
 420          *
 421          * @access      private
 422          * @return      string
 423          */
 424         function _error_message()
 425         {
 426                 return sqlite_error_string(sqlite_last_error($this->conn_id));
 427         }
 428
 429         // --------------------------------------------------------------------
 430
 431         /**
 432          * The error message number
 433          *
 434          * @access      private
 435          * @return      integer
 436          */
 437         function _error_number()
 438         {
 439                 return sqlite_last_error($this->conn_id);
 440         }
 441
 442         // --------------------------------------------------------------------
 443
 444         /**
 445          * Escape the SQL Identifiers
 446          *
 447          * This function escapes column and table names
 448          *
 449          * @access      private
 450          * @param       string
 451          * @return      string
 452          */
 453         function _escape_identifiers($item)
 454         {
 455                 if ($this->_escape_char == '')
 456                 {
 457                         return $item;
 458                 }
 459
 460                 foreach ($this->_reserved_identifiers as $id)
 461                 {
 462                         if (strpos($item, '.'.$id) !== FALSE)
 463                         {
 464                                 $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item);
 465
 466                                 // remove duplicates if the user already included the escape
 467                                 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
 468                         }
 469                 }
 470
 471                 if (strpos($item, '.') !== FALSE)
 472                 {
 473                         $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;
 474                 }
 475                 else
 476                 {
 477                         $str = $this->_escape_char.$item.$this->_escape_char;
 478                 }
 479
 480                 // remove duplicates if the user already included the escape
 481                 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
 482         }
 483
 484         // --------------------------------------------------------------------
 485
 486         /**
 487          * From Tables
 488          *
 489          * This function implicitly groups FROM tables so there is no confusion
 490          * about operator precedence in harmony with SQL standards
 491          *
 492          * @access      public
 493          * @param       type
 494          * @return      type
 495          */
 496         function _from_tables($tables)
 497         {
 498                 if ( ! is_array($tables))
 499                 {
 500                         $tables = array($tables);
 501                 }
 502
 503                 return '('.implode(', ', $tables).')';
 504         }
 505
 506         // --------------------------------------------------------------------
 507
 508         /**
 509          * Insert statement
 510          *
 511          * Generates a platform-specific insert string from the supplied data
 512          *
 513          * @access      public
 514          * @param       string  the table name
 515          * @param       array   the insert keys
 516          * @param       array   the insert values
 517          * @return      string
 518          */
 519         function _insert($table, $keys, $values)
 520         {
 521                 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
 522         }
 523
 524         // --------------------------------------------------------------------
 525
 526         /**
 527          * Update statement
 528          *
 529          * Generates a platform-specific update string from the supplied data
 530          *
 531          * @access      public
 532          * @param       string  the table name
 533          * @param       array   the update data
 534          * @param       array   the where clause
 535          * @param       array   the orderby clause
 536          * @param       array   the limit clause
 537          * @return      string
 538          */
 539         function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
 540         {
 541                 foreach ($values as $key => $val)
 542                 {
 543                         $valstr[] = $key." = ".$val;
 544                 }
 545
 546                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 547
 548                 $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
 549
 550                 $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
 551
 552                 $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
 553
 554                 $sql .= $orderby.$limit;
 555
 556                 return $sql;
 557         }
 558
 559
 560         // --------------------------------------------------------------------
 561
 562         /**
 563          * Truncate statement
 564          *
 565          * Generates a platform-specific truncate string from the supplied data
 566          * If the database does not support the truncate() command
 567          * This function maps to "DELETE FROM table"
 568          *
 569          * @access      public
 570          * @param       string  the table name
 571          * @return      string
 572          */
 573         function _truncate($table)
 574         {
 575                 return $this->_delete($table);
 576         }
 577
 578         // --------------------------------------------------------------------
 579
 580         /**
 581          * Delete statement
 582          *
 583          * Generates a platform-specific delete string from the supplied data
 584          *
 585          * @access      public
 586          * @param       string  the table name
 587          * @param       array   the where clause
 588          * @param       string  the limit clause
 589          * @return      string
 590          */
 591         function _delete($table, $where = array(), $like = array(), $limit = FALSE)
 592         {
 593                 $conditions = '';
 594
 595                 if (count($where) > 0 OR count($like) > 0)
 596                 {
 597                         $conditions = "\nWHERE ";
 598                         $conditions .= implode("\n", $this->ar_where);
 599
 600                         if (count($where) > 0 && count($like) > 0)
 601                         {
 602                                 $conditions .= " AND ";
 603                         }
 604                         $conditions .= implode("\n", $like);
 605                 }
 606
 607                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 608
 609                 return "DELETE FROM ".$table.$conditions.$limit;
 610         }
 611
 612         // --------------------------------------------------------------------
 613
 614         /**
 615          * Limit string
 616          *
 617          * Generates a platform-specific LIMIT clause
 618          *
 619          * @access      public
 620          * @param       string  the sql query string
 621          * @param       integer the number of rows to limit the query to
 622          * @param       integer the offset value
 623          * @return      string
 624          */
 625         function _limit($sql, $limit, $offset)
 626         {
 627                 if ($offset == 0)
 628                 {
 629                         $offset = '';
 630                 }
 631                 else
 632                 {
 633                         $offset .= ", ";
 634                 }
 635
 636                 return $sql."LIMIT ".$offset.$limit;
 637         }
 638
 639         // --------------------------------------------------------------------
 640
 641         /**
 642          * Close DB Connection
 643          *
 644          * @access      public
 645          * @param       resource
 646          * @return      void
 647          */
 648         function _close($conn_id)
 649         {
 650                 @sqlite_close($conn_id);
 651         }
 652
 653
 654 }
 655
 656
 657 /* End of file sqlite_driver.php */
 658 /* Location: ./system/database/drivers/sqlite/sqlite_driver.php */