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