library/Zend/Db/Adapter/Pdo/Sqlite.php

   1 <?php
   2 /**
   3  * Zend Framework
   4  *
   5  * LICENSE
   6  *
   7  * This source file is subject to the new BSD license that is bundled
   8  * with this package in the file LICENSE.txt.
   9  * It is also available through the world-wide-web at this URL:
  10  * http://framework.zend.com/license/new-bsd
  11  * If you did not receive a copy of the license and are unable to
  12  * obtain it through the world-wide-web, please send an email
  13  * to license@zend.com so we can send you a copy immediately.
  14  *
  15  * @category   Zend
  16  * @package    Zend_Db
  17  * @subpackage Adapter
  18  * @copyright  Copyright (c) 2005-2009 Zend Technologies USA Inc. (http://www.zend.com)
  19  * @license    http://framework.zend.com/license/new-bsd     New BSD License
  20  * @version    $Id: Sqlite.php 16203 2009-06-21 18:56:17Z thomas $
  21  */
  22
  23
  24 /**
  25  * @see Zend_Db_Adapter_Pdo_Abstract
  26  */
  27 require_once 'Zend/Db/Adapter/Pdo/Abstract.php';
  28
  29
  30 /**
  31  * Class for connecting to SQLite2 and SQLite3 databases and performing common operations.
  32  *
  33  * @category   Zend
  34  * @package    Zend_Db
  35  * @subpackage Adapter
  36  * @copyright  Copyright (c) 2005-2009 Zend Technologies USA Inc. (http://www.zend.com)
  37  * @license    http://framework.zend.com/license/new-bsd     New BSD License
  38  */
  39 class Zend_Db_Adapter_Pdo_Sqlite extends Zend_Db_Adapter_Pdo_Abstract
  40 {
  41
  42     /**
  43      * PDO type
  44      *
  45      * @var string
  46      */
  47      protected $_pdoType = 'sqlite';
  48
  49     /**
  50      * Keys are UPPERCASE SQL datatypes or the constants
  51      * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
  52      *
  53      * Values are:
  54      * 0 = 32-bit integer
  55      * 1 = 64-bit integer
  56      * 2 = float or decimal
  57      *
  58      * @var array Associative array of datatypes to values 0, 1, or 2.
  59      */
  60     protected $_numericDataTypes = array(
  61         Zend_Db::INT_TYPE    => Zend_Db::INT_TYPE,
  62         Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
  63         Zend_Db::FLOAT_TYPE  => Zend_Db::FLOAT_TYPE,
  64         'INTEGER'            => Zend_Db::BIGINT_TYPE,
  65         'REAL'               => Zend_Db::FLOAT_TYPE
  66     );
  67
  68     /**
  69      * Constructor.
  70      *
  71      * $config is an array of key/value pairs containing configuration
  72      * options.  Note that the SQLite options are different than most of
  73      * the other PDO adapters in that no username or password are needed.
  74      * Also, an extra config key "sqlite2" specifies compatibility mode.
  75      *
  76      * dbname    => (string) The name of the database to user (required,
  77      *                       use :memory: for memory-based database)
  78      *
  79      * sqlite2   => (boolean) PDO_SQLITE defaults to SQLite 3.  For compatibility
  80      *                        with an older SQLite 2 database, set this to TRUE.
  81      *
  82      * @param array $config An array of configuration keys.
  83      */
  84     public function __construct(array $config = array())
  85     {
  86         if (isset($config['sqlite2']) && $config['sqlite2']) {
  87             $this->_pdoType = 'sqlite2';
  88         }
  89
  90         // SQLite uses no username/password.  Stub to satisfy parent::_connect()
  91         $this->_config['username'] = null;
  92         $this->_config['password'] = null;
  93
  94         return parent::__construct($config);
  95     }
  96
  97     /**
  98      * Check for config options that are mandatory.
  99      * Throw exceptions if any are missing.
 100      *
 101      * @param array $config
 102      * @throws Zend_Db_Adapter_Exception
 103      */
 104     protected function _checkRequiredOptions(array $config)
 105     {
 106         // we need at least a dbname
 107         if (! array_key_exists('dbname', $config)) {
 108             /** @see Zend_Db_Adapter_Exception */
 109             require_once 'Zend/Db/Adapter/Exception.php';
 110             throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'dbname' that names the database instance");
 111         }
 112     }
 113
 114     /**
 115      * DSN builder
 116      */
 117     protected function _dsn()
 118     {
 119         return $this->_pdoType .':'. $this->_config['dbname'];
 120     }
 121
 122     /**
 123      * Special configuration for SQLite behavior: make sure that result sets
 124      * contain keys like 'column' instead of 'table.column'.
 125      *
 126      * @throws Zend_Db_Adapter_Exception
 127      */
 128     protected function _connect()
 129     {
 130         /**
 131          * if we already have a PDO object, no need to re-connect.
 132          */
 133         if ($this->_connection) {
 134             return;
 135         }
 136
 137         parent::_connect();
 138
 139         $retval = $this->_connection->exec('PRAGMA full_column_names=0');
 140         if ($retval === false) {
 141             $error = $this->_connection->errorInfo();
 142             /** @see Zend_Db_Adapter_Exception */
 143             require_once 'Zend/Db/Adapter/Exception.php';
 144             throw new Zend_Db_Adapter_Exception($error[2]);
 145         }
 146
 147         $retval = $this->_connection->exec('PRAGMA short_column_names=1');
 148         if ($retval === false) {
 149             $error = $this->_connection->errorInfo();
 150             /** @see Zend_Db_Adapter_Exception */
 151             require_once 'Zend/Db/Adapter/Exception.php';
 152             throw new Zend_Db_Adapter_Exception($error[2]);
 153         }
 154     }
 155
 156     /**
 157      * Returns a list of the tables in the database.
 158      *
 159      * @return array
 160      */
 161     public function listTables()
 162     {
 163         $sql = "SELECT name FROM sqlite_master WHERE type='table' "
 164              . "UNION ALL SELECT name FROM sqlite_temp_master "
 165              . "WHERE type='table' ORDER BY name";
 166
 167         return $this->fetchCol($sql);
 168     }
 169
 170     /**
 171      * Returns the column descriptions for a table.
 172      *
 173      * The return value is an associative array keyed by the column name,
 174      * as returned by the RDBMS.
 175      *
 176      * The value of each array element is an associative array
 177      * with the following keys:
 178      *
 179      * SCHEMA_NAME      => string; name of database or schema
 180      * TABLE_NAME       => string;
 181      * COLUMN_NAME      => string; column name
 182      * COLUMN_POSITION  => number; ordinal position of column in table
 183      * DATA_TYPE        => string; SQL datatype name of column
 184      * DEFAULT          => string; default expression of column, null if none
 185      * NULLABLE         => boolean; true if column can have nulls
 186      * LENGTH           => number; length of CHAR/VARCHAR
 187      * SCALE            => number; scale of NUMERIC/DECIMAL
 188      * PRECISION        => number; precision of NUMERIC/DECIMAL
 189      * UNSIGNED         => boolean; unsigned property of an integer type
 190      * PRIMARY          => boolean; true if column is part of the primary key
 191      * PRIMARY_POSITION => integer; position of column in primary key
 192      * IDENTITY         => integer; true if column is auto-generated with unique values
 193      *
 194      * @param string $tableName
 195      * @param string $schemaName OPTIONAL
 196      * @return array
 197      */
 198     public function describeTable($tableName, $schemaName = null)
 199     {
 200         if ($schemaName) {
 201             $sql = "PRAGMA $schemaName.table_info($tableName)";
 202         } else {
 203             $sql = "PRAGMA table_info($tableName)";
 204         }
 205
 206         $stmt = $this->query($sql);
 207
 208         /**
 209          * Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
 210          */
 211         $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
 212
 213         $cid        = 0;
 214         $name       = 1;
 215         $type       = 2;
 216         $notnull    = 3;
 217         $dflt_value = 4;
 218         $pk         = 5;
 219
 220         $desc = array();
 221
 222         $p = 1;
 223         foreach ($result as $key => $row) {
 224             list($length, $scale, $precision, $primary, $primaryPosition, $identity) =
 225                 array(null, null, null, false, null, false);
 226             if (preg_match('/^((?:var)?char)\((\d+)\)/i', $row[$type], $matches)) {
 227                 $row[$type] = $matches[1];
 228                 $length = $matches[2];
 229             } else if (preg_match('/^decimal\((\d+),(\d+)\)/i', $row[$type], $matches)) {
 230                 $row[$type] = 'DECIMAL';
 231                 $precision = $matches[1];
 232                 $scale = $matches[2];
 233             }
 234             if ((bool) $row[$pk]) {
 235                 $primary = true;
 236                 $primaryPosition = $p;
 237                 /**
 238                  * SQLite INTEGER primary key is always auto-increment.
 239                  */
 240                 $identity = (bool) ($row[$type] == 'INTEGER');
 241                 ++$p;
 242             }
 243             $desc[$this->foldCase($row[$name])] = array(
 244                 'SCHEMA_NAME'      => $this->foldCase($schemaName),
 245                 'TABLE_NAME'       => $this->foldCase($tableName),
 246                 'COLUMN_NAME'      => $this->foldCase($row[$name]),
 247                 'COLUMN_POSITION'  => $row[$cid]+1,
 248                 'DATA_TYPE'        => $row[$type],
 249                 'DEFAULT'          => $row[$dflt_value],
 250                 'NULLABLE'         => ! (bool) $row[$notnull],
 251                 'LENGTH'           => $length,
 252                 'SCALE'            => $scale,
 253                 'PRECISION'        => $precision,
 254                 'UNSIGNED'         => null, // Sqlite3 does not support unsigned data
 255                 'PRIMARY'          => $primary,
 256                 'PRIMARY_POSITION' => $primaryPosition,
 257                 'IDENTITY'         => $identity
 258             );
 259         }
 260         return $desc;
 261     }
 262
 263     /**
 264      * Adds an adapter-specific LIMIT clause to the SELECT statement.
 265      *
 266      * @param string $sql
 267      * @param integer $count
 268      * @param integer $offset OPTIONAL
 269      * @return string
 270      */
 271     public function limit($sql, $count, $offset = 0)
 272     {
 273         $count = intval($count);
 274         if ($count <= 0) {
 275             /** @see Zend_Db_Adapter_Exception */
 276             require_once 'Zend/Db/Adapter/Exception.php';
 277             throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
 278         }
 279
 280         $offset = intval($offset);
 281         if ($offset < 0) {
 282             /** @see Zend_Db_Adapter_Exception */
 283             require_once 'Zend/Db/Adapter/Exception.php';
 284             throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
 285         }
 286
 287         $sql .= " LIMIT $count";
 288         if ($offset > 0) {
 289             $sql .= " OFFSET $offset";
 290         }
 291
 292         return $sql;
 293     }
 294
 295 }