00001 <?php
00002 /* SVN FILE: $Id: dbo__sqlite_8php-source.html 675 2008-12-26 00:27:14Z gwoo $ */
00003 
00004 /**
00005  * SQLite layer for DBO
00006  *
00007  * Long description for file
00008  *
00009  * PHP versions 4 and 5
00010  *
00011  * CakePHP(tm) :  Rapid Development Framework <http://www.cakephp.org/>
00012  * Copyright 2005-2008, Cake Software Foundation, Inc.
00013  *                              1785 E. Sahara Avenue, Suite 490-204
00014  *                              Las Vegas, Nevada 89104
00015  *
00016  * Licensed under The MIT License
00017  * Redistributions of files must retain the above copyright notice.
00018  *
00019  * @filesource
00020  * @copyright       Copyright 2005-2008, Cake Software Foundation, Inc.
00021  * @link                http://www.cakefoundation.org/projects/info/cakephp CakePHP(tm) Project
00022  * @package         cake
00023  * @subpackage      cake.cake.libs.model.dbo
00024  * @since           CakePHP(tm) v 0.9.0
00025  * @version         $Revision: 675 $
00026  * @modifiedby      $LastChangedBy: gwoo $
00027  * @lastmodified    $Date: 2008-12-25 16:27:14 -0800 (Thu, 25 Dec 2008) $
00028  * @license         http://www.opensource.org/licenses/mit-license.php The MIT License
00029  */
00030 /**
00031  * DBO implementation for the SQLite DBMS.
00032  *
00033  * Long description for class
00034  *
00035  * @package     cake
00036  * @subpackage  cake.cake.libs.model.dbo
00037  */
00038 class DboSqlite extends DboSource {
00039 
00040 /**
00041  * Enter description here...
00042  *
00043  * @var unknown_type
00044  */
00045     var $description = "SQLite DBO Driver";
00046 /**
00047  * Enter description here...
00048  *
00049  * @var unknown_type
00050  */
00051     var $startQuote = '"';
00052 /**
00053  * Enter description here...
00054  *
00055  * @var unknown_type
00056  */
00057     var $endQuote = '"';
00058 /**
00059  * Base configuration settings for SQLite driver
00060  *
00061  * @var array
00062  */
00063     var $_baseConfig = array(
00064         'persistent' => true,
00065         'database' => null,
00066         'connect' => 'sqlite_popen'
00067     );
00068 /**
00069  * SQLite column definition
00070  *
00071  * @var array
00072  */
00073     var $columns = array(
00074         'primary_key' => array('name' => 'integer primary key'),
00075         'string' => array('name' => 'varchar', 'limit' => '255'),
00076         'text' => array('name' => 'text'),
00077         'integer' => array('name' => 'integer', 'limit' => '11', 'formatter' => 'intval'),
00078         'float' => array('name' => 'float', 'formatter' => 'floatval'),
00079         'datetime' => array('name' => 'timestamp', 'format' => 'YmdHis', 'formatter' => 'date'),
00080         'timestamp' => array('name' => 'timestamp', 'format' => 'YmdHis', 'formatter' => 'date'),
00081         'time' => array('name' => 'timestamp', 'format' => 'His', 'formatter' => 'date'),
00082         'date' => array('name' => 'date', 'format' => 'Ymd', 'formatter' => 'date'),
00083         'binary' => array('name' => 'blob'),
00084         'boolean' => array('name' => 'integer', 'limit' => '1')
00085     );
00086 /**
00087  * Connects to the database using config['database'] as a filename.
00088  *
00089  * @param array $config Configuration array for connecting
00090  * @return mixed
00091  */
00092     function connect() {
00093         $config = $this->config;
00094         $this->connection = $config['connect']($config['database']);
00095         $this->connected = is_resource($this->connection);
00096         return $this->connected;
00097     }
00098 /**
00099  * Disconnects from database.
00100  *
00101  * @return boolean True if the database could be disconnected, else false
00102  */
00103     function disconnect() {
00104         @sqlite_close($this->connection);
00105         $this->connected = false;
00106         return $this->connected;
00107     }
00108 /**
00109  * Executes given SQL statement.
00110  *
00111  * @param string $sql SQL statement
00112  * @return resource Result resource identifier
00113  */
00114     function _execute($sql) {
00115         return sqlite_query($this->connection, $sql);
00116     }
00117 /**
00118  * Returns an array of tables in the database. If there are no tables, an error is raised and the application exits.
00119  *
00120  * @return array Array of tablenames in the database
00121  */
00122     function listSources() {
00123         $db = $this->config['database'];
00124         $this->config['database'] = basename($this->config['database']);
00125 
00126         $cache = parent::listSources();
00127         if ($cache != null) {
00128             return $cache;
00129         }
00130 
00131         $result = $this->fetchAll("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
00132 
00133         if (!$result || empty($result)) {
00134             return array();
00135         } else {
00136             $tables = array();
00137             foreach ($result as $table) {
00138                 $tables[] = $table[0]['name'];
00139             }
00140             parent::listSources($tables);
00141 
00142             $this->config['database'] = $db;
00143             return $tables;
00144         }
00145         $this->config['database'] = $db;
00146         return array();
00147     }
00148 /**
00149  * Returns an array of the fields in given table name.
00150  *
00151  * @param string $tableName Name of database table to inspect
00152  * @return array Fields in table. Keys are name and type
00153  */
00154     function describe(&$model) {
00155         $cache = parent::describe($model);
00156         if ($cache != null) {
00157             return $cache;
00158         }
00159         $fields = array();
00160         $result = $this->fetchAll('PRAGMA table_info(' . $model->tablePrefix . $model->table . ')');
00161 
00162         foreach ($result as $column) {
00163             $fields[$column[0]['name']] = array(
00164                 'type' => $this->column($column[0]['type']),
00165                 'null' => ! $column[0]['notnull'],
00166                 'default' => $column[0]['dflt_value']
00167             );
00168         }
00169 
00170         $this->__cacheDescription($model->tablePrefix . $model->table, $fields);
00171         return $fields;
00172     }
00173 /**
00174  * Returns a quoted and escaped string of $data for use in an SQL statement.
00175  *
00176  * @param string $data String to be prepared for use in an SQL statement
00177  * @return string Quoted and escaped
00178  */
00179     function value ($data, $column = null, $safe = false) {
00180         $parent = parent::value($data, $column, $safe);
00181 
00182         if ($parent != null) {
00183             return $parent;
00184         }
00185 
00186         if ($data === null) {
00187             return 'NULL';
00188         }
00189 
00190         if ($data === '') {
00191             return  "''";
00192         }
00193 
00194         switch ($column) {
00195             case 'boolean':
00196                 $data = $this->boolean((bool)$data);
00197             break;
00198             default:
00199                 $data = sqlite_escape_string($data);
00200             break;
00201         }
00202         return "'" . $data . "'";
00203     }
00204 /**
00205  * Begin a transaction
00206  *
00207  * @param unknown_type $model
00208  * @return boolean True on success, false on fail
00209  * (i.e. if the database/model does not support transactions).
00210  */
00211     function begin (&$model) {
00212         if (parent::begin($model)) {
00213             if ($this->execute('BEGIN')) {
00214                 $this->_transactionStarted = true;
00215                 return true;
00216             }
00217         }
00218         return false;
00219     }
00220 /**
00221  * Commit a transaction
00222  *
00223  * @param unknown_type $model
00224  * @return boolean True on success, false on fail
00225  * (i.e. if the database/model does not support transactions,
00226  * or a transaction has not started).
00227  */
00228     function commit (&$model) {
00229         if (parent::commit($model)) {
00230             $this->_transactionStarted = false;
00231             return $this->execute('COMMIT');
00232         }
00233         return false;
00234     }
00235 /**
00236  * Rollback a transaction
00237  *
00238  * @param unknown_type $model
00239  * @return boolean True on success, false on fail
00240  * (i.e. if the database/model does not support transactions,
00241  * or a transaction has not started).
00242  */
00243     function rollback (&$model) {
00244         if (parent::rollback($model)) {
00245             return $this->execute('ROLLBACK');
00246         }
00247         return false;
00248     }
00249 /**
00250  * Returns a formatted error message from previous database operation.
00251  *
00252  * @return string Error message
00253  */
00254     function lastError() {
00255         $error = sqlite_last_error($this->connection);
00256         if ($error) {
00257             return $error.': '.sqlite_error_string($error);
00258         }
00259         return null;
00260     }
00261 /**
00262  * Returns number of affected rows in previous database operation. If no previous operation exists, this returns false.
00263  *
00264  * @return int Number of affected rows
00265  */
00266     function lastAffected() {
00267         if ($this->_result) {
00268             return sqlite_changes($this->connection);
00269         }
00270         return false;
00271     }
00272 /**
00273  * Returns number of rows in previous resultset. If no previous resultset exists,
00274  * this returns false.
00275  *
00276  * @return int Number of rows in resultset
00277  */
00278     function lastNumRows() {
00279         if ($this->_result) {
00280             sqlite_num_rows($this->_result);
00281         }
00282         return false;
00283     }
00284 /**
00285  * Returns the ID generated from the previous INSERT operation.
00286  *
00287  * @return int
00288  */
00289     function lastInsertId() {
00290         return sqlite_last_insert_rowid($this->connection);
00291     }
00292 /**
00293  * Converts database-layer column types to basic types
00294  *
00295  * @param string $real Real database-layer column type (i.e. "varchar(255)")
00296  * @return string Abstract column type (i.e. "string")
00297  */
00298     function column($real) {
00299         if (is_array($real)) {
00300             $col = $real['name'];
00301             if (isset($real['limit'])) {
00302                 $col .= '('.$real['limit'].')';
00303             }
00304             return $col;
00305         }
00306 
00307         $col = low(r(')', '', $real));
00308         $limit = null;
00309         @list($col, $limit) = explode('(', $col);
00310 
00311         if (in_array($col, array('text', 'integer', 'float', 'boolean', 'timestamp', 'datetime'))) {
00312             return $col;
00313         }
00314         if (strpos($col, 'varchar') !== false) {
00315             return 'string';
00316         }
00317         if (in_array($col, array('blob', 'clob'))) {
00318             return 'binary';
00319         }
00320         if (strpos($col, 'numeric') !== false) {
00321             return 'float';
00322         }
00323 
00324         return 'text';
00325     }
00326 /**
00327  * Enter description here...
00328  *
00329  * @param unknown_type $results
00330  */
00331     function resultSet(&$results) {
00332         $this->results =& $results;
00333         $this->map = array();
00334         $num_fields = sqlite_num_fields($results);
00335         $index = 0;
00336         $j = 0;
00337 
00338         while ($j < $num_fields) {
00339             $columnName = str_replace('"', '', sqlite_field_name($results, $j));
00340 
00341             if (strpos($columnName, '.')) {
00342                 $parts = explode('.', $columnName);
00343                 $this->map[$index++] = array($parts[0], $parts[1]);
00344             } else {
00345                 $this->map[$index++] = array(0, $columnName);
00346             }
00347             $j++;
00348         }
00349     }
00350 /**
00351  * Fetches the next row from the current result set
00352  *
00353  * @return unknown
00354  */
00355     function fetchResult() {
00356         if ($row = sqlite_fetch_array($this->results, SQLITE_ASSOC)) {
00357             $resultRow = array();
00358             $i = 0;
00359 
00360             foreach ($row as $index => $field) {
00361                 if (strpos($index, '.')) {
00362                     list($table, $column) = explode('.', str_replace('"', '', $index));
00363                     $resultRow[$table][$column] = $row[$index];
00364                 } else {
00365                     $resultRow[0][str_replace('"', '', $index)] = $row[$index];
00366                 }
00367                 $i++;
00368             }
00369             return $resultRow;
00370         } else {
00371             return false;
00372         }
00373     }
00374 /**
00375  * Returns a limit statement in the correct format for the particular database.
00376  *
00377  * @param int $limit Limit of results returned
00378  * @param int $offset Offset from which to start results
00379  * @return string SQL limit/offset statement
00380  */
00381     function limit ($limit, $offset = null) {
00382         if ($limit) {
00383             $rt = '';
00384             if (!strpos(strtolower($limit), 'limit') || strpos(strtolower($limit), 'limit') === 0) {
00385                 $rt = ' LIMIT';
00386             }
00387             $rt .= ' ' . $limit;
00388             if ($offset) {
00389                 $rt .= ' OFFSET ' . $offset;
00390             }
00391             return $rt;
00392         }
00393         return null;
00394     }
00395 /**
00396  * Inserts multiple values into a join table
00397  *
00398  * @param string $table
00399  * @param string $fields
00400  * @param array $values
00401  */
00402     function insertMulti($table, $fields, $values) {
00403         $count = count($values);
00404         for ($x = 0; $x < $count; $x++) {
00405             $this->query("INSERT INTO {$table} ({$fields}) VALUES {$values[$x]}");
00406         }
00407     }
00408 }
00409 ?>