Connection.php 20.5 KB
Newer Older
w  
Qiang Xue committed
1 2 3
<?php
/**
 * @link http://www.yiiframework.com/
Qiang Xue committed
4
 * @copyright Copyright (c) 2008 Yii Software LLC
w  
Qiang Xue committed
5 6 7
 * @license http://www.yiiframework.com/license/
 */

Qiang Xue committed
8
namespace yii\db;
w  
Qiang Xue committed
9

Qiang Xue committed
10 11
use PDO;
use Yii;
12
use yii\base\Component;
Qiang Xue committed
13 14
use yii\base\InvalidConfigException;
use yii\base\NotSupportedException;
15
use yii\caching\Cache;
w  
Qiang Xue committed
16

w  
Qiang Xue committed
17
/**
w  
Qiang Xue committed
18
 * Connection represents a connection to a database via [PDO](http://www.php.net/manual/en/ref.pdo.php).
w  
Qiang Xue committed
19
 *
w  
Qiang Xue committed
20 21 22
 * Connection works together with [[Command]], [[DataReader]] and [[Transaction]]
 * to provide data access to various DBMS in a common set of APIs. They are a thin wrapper
 * of the [[PDO PHP extension]](http://www.php.net/manual/en/ref.pdo.php).
w  
Qiang Xue committed
23
 *
w  
Qiang Xue committed
24
 * To establish a DB connection, set [[dsn]], [[username]] and [[password]], and then
25
 * call [[open()]] to be true.
w  
Qiang Xue committed
26 27
 *
 * The following example shows how to create a Connection instance and establish
w  
Qiang Xue committed
28
 * the DB connection:
w  
Qiang Xue committed
29
 *
w  
Qiang Xue committed
30
 * ~~~
Alexander Makarov committed
31
 * $connection = new \yii\db\Connection([
Qiang Xue committed
32 33 34
 *     'dsn' => $dsn,
 *     'username' => $username,
 *     'password' => $password,
Alexander Makarov committed
35
 * ]);
36
 * $connection->open();
w  
Qiang Xue committed
37 38
 * ~~~
 *
Qiang Xue committed
39
 * After the DB connection is established, one can execute SQL statements like the following:
w  
Qiang Xue committed
40 41 42
 *
 * ~~~
 * $command = $connection->createCommand('SELECT * FROM tbl_post');
Qiang Xue committed
43 44 45
 * $posts = $command->queryAll();
 * $command = $connection->createCommand('UPDATE tbl_post SET status=1');
 * $command->execute();
w  
Qiang Xue committed
46 47
 * ~~~
 *
Qiang Xue committed
48 49 50
 * One can also do prepared SQL execution and bind parameters to the prepared SQL.
 * When the parameters are coming from user input, you should use this approach
 * to prevent SQL injection attacks. The following is an example:
w  
Qiang Xue committed
51
 *
w  
Qiang Xue committed
52 53 54 55 56
 * ~~~
 * $command = $connection->createCommand('SELECT * FROM tbl_post WHERE id=:id');
 * $command->bindValue(':id', $_GET['id']);
 * $post = $command->query();
 * ~~~
w  
Qiang Xue committed
57
 *
Qiang Xue committed
58 59 60 61
 * For more information about how to perform various DB queries, please refer to [[Command]].
 *
 * If the underlying DBMS supports transactions, you can perform transactional SQL queries
 * like the following:
w  
Qiang Xue committed
62
 *
w  
Qiang Xue committed
63 64 65
 * ~~~
 * $transaction = $connection->beginTransaction();
 * try {
66 67 68 69
 *     $connection->createCommand($sql1)->execute();
 *     $connection->createCommand($sql2)->execute();
 *     // ... executing other SQL statements ...
 *     $transaction->commit();
70
 * } catch (Exception $e) {
71
 *     $transaction->rollBack();
w  
Qiang Xue committed
72
 * }
w  
Qiang Xue committed
73
 * ~~~
w  
Qiang Xue committed
74
 *
75
 * Connection is often used as an application component and configured in the application
Qiang Xue committed
76
 * configuration like the following:
w  
Qiang Xue committed
77 78
 *
 * ~~~
Alexander Makarov committed
79 80 81
 * [
 *	 'components' => [
 *		 'db' => [
Qiang Xue committed
82
 *			 'class' => '\yii\db\Connection',
Qiang Xue committed
83 84 85 86
 *			 'dsn' => 'mysql:host=127.0.0.1;dbname=demo',
 *			 'username' => 'root',
 *			 'password' => '',
 *			 'charset' => 'utf8',
Alexander Makarov committed
87 88 89
 *		 ],
 *	 ],
 * ]
w  
Qiang Xue committed
90
 * ~~~
w  
Qiang Xue committed
91
 *
92
 * @property string $driverName Name of the DB driver. This property is read-only.
93
 * @property boolean $isActive Whether the DB connection is established. This property is read-only.
94 95 96 97 98 99 100 101
 * @property string $lastInsertID The row ID of the last row inserted, or the last value retrieved from the
 * sequence object. This property is read-only.
 * @property QueryBuilder $queryBuilder The query builder for the current DB connection. This property is
 * read-only.
 * @property Schema $schema The schema information for the database opened by this connection. This property
 * is read-only.
 * @property Transaction $transaction The currently active transaction. Null if no active transaction. This
 * property is read-only.
Qiang Xue committed
102
 *
w  
Qiang Xue committed
103 104 105
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
106
class Connection extends Component
w  
Qiang Xue committed
107
{
108 109 110 111
    /**
     * @event Event an event that is triggered after a DB connection is established
     */
    const EVENT_AFTER_OPEN = 'afterOpen';
112

113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263
    /**
     * @var string the Data Source Name, or DSN, contains the information required to connect to the database.
     * Please refer to the [PHP manual](http://www.php.net/manual/en/function.PDO-construct.php) on
     * the format of the DSN string.
     * @see charset
     */
    public $dsn;
    /**
     * @var string the username for establishing DB connection. Defaults to `null` meaning no username to use.
     */
    public $username;
    /**
     * @var string the password for establishing DB connection. Defaults to `null` meaning no password to use.
     */
    public $password;
    /**
     * @var array PDO attributes (name => value) that should be set when calling [[open()]]
     * to establish a DB connection. Please refer to the
     * [PHP manual](http://www.php.net/manual/en/function.PDO-setAttribute.php) for
     * details about available attributes.
     */
    public $attributes;
    /**
     * @var PDO the PHP PDO instance associated with this DB connection.
     * This property is mainly managed by [[open()]] and [[close()]] methods.
     * When a DB connection is active, this property will represent a PDO instance;
     * otherwise, it will be null.
     */
    public $pdo;
    /**
     * @var boolean whether to enable schema caching.
     * Note that in order to enable truly schema caching, a valid cache component as specified
     * by [[schemaCache]] must be enabled and [[enableSchemaCache]] must be set true.
     * @see schemaCacheDuration
     * @see schemaCacheExclude
     * @see schemaCache
     */
    public $enableSchemaCache = false;
    /**
     * @var integer number of seconds that table metadata can remain valid in cache.
     * Use 0 to indicate that the cached data will never expire.
     * @see enableSchemaCache
     */
    public $schemaCacheDuration = 3600;
    /**
     * @var array list of tables whose metadata should NOT be cached. Defaults to empty array.
     * The table names may contain schema prefix, if any. Do not quote the table names.
     * @see enableSchemaCache
     */
    public $schemaCacheExclude = [];
    /**
     * @var Cache|string the cache object or the ID of the cache application component that
     * is used to cache the table metadata.
     * @see enableSchemaCache
     */
    public $schemaCache = 'cache';
    /**
     * @var boolean whether to enable query caching.
     * Note that in order to enable query caching, a valid cache component as specified
     * by [[queryCache]] must be enabled and [[enableQueryCache]] must be set true.
     *
     * Methods [[beginCache()]] and [[endCache()]] can be used as shortcuts to turn on
     * and off query caching on the fly.
     * @see queryCacheDuration
     * @see queryCache
     * @see queryCacheDependency
     * @see beginCache()
     * @see endCache()
     */
    public $enableQueryCache = false;
    /**
     * @var integer number of seconds that query results can remain valid in cache.
     * Defaults to 3600, meaning 3600 seconds, or one hour.
     * Use 0 to indicate that the cached data will never expire.
     * @see enableQueryCache
     */
    public $queryCacheDuration = 3600;
    /**
     * @var \yii\caching\Dependency the dependency that will be used when saving query results into cache.
     * Defaults to null, meaning no dependency.
     * @see enableQueryCache
     */
    public $queryCacheDependency;
    /**
     * @var Cache|string the cache object or the ID of the cache application component
     * that is used for query caching.
     * @see enableQueryCache
     */
    public $queryCache = 'cache';
    /**
     * @var string the charset used for database connection. The property is only used
     * for MySQL, PostgreSQL and CUBRID databases. Defaults to null, meaning using default charset
     * as specified by the database.
     *
     * Note that if you're using GBK or BIG5 then it's highly recommended to
     * specify charset via DSN like 'mysql:dbname=mydatabase;host=127.0.0.1;charset=GBK;'.
     */
    public $charset;
    /**
     * @var boolean whether to turn on prepare emulation. Defaults to false, meaning PDO
     * will use the native prepare support if available. For some databases (such as MySQL),
     * this may need to be set true so that PDO can emulate the prepare support to bypass
     * the buggy native prepare support.
     * The default value is null, which means the PDO ATTR_EMULATE_PREPARES value will not be changed.
     */
    public $emulatePrepare;
    /**
     * @var string the common prefix or suffix for table names. If a table name is given
     * as `{{%TableName}}`, then the percentage character `%` will be replaced with this
     * property value. For example, `{{%post}}` becomes `{{tbl_post}}`.
     */
    public $tablePrefix = 'tbl_';
    /**
     * @var array mapping between PDO driver names and [[Schema]] classes.
     * The keys of the array are PDO driver names while the values the corresponding
     * schema class name or configuration. Please refer to [[Yii::createObject()]] for
     * details on how to specify a configuration.
     *
     * This property is mainly used by [[getSchema()]] when fetching the database schema information.
     * You normally do not need to set this property unless you want to use your own
     * [[Schema]] class to support DBMS that is not supported by Yii.
     */
    public $schemaMap = [
        'pgsql' => 'yii\db\pgsql\Schema',    // PostgreSQL
        'mysqli' => 'yii\db\mysql\Schema',   // MySQL
        'mysql' => 'yii\db\mysql\Schema',    // MySQL
        'sqlite' => 'yii\db\sqlite\Schema',  // sqlite 3
        'sqlite2' => 'yii\db\sqlite\Schema', // sqlite 2
        'sqlsrv' => 'yii\db\mssql\Schema',   // newer MSSQL driver on MS Windows hosts
        'oci' => 'yii\db\oci\Schema',        // Oracle driver
        'mssql' => 'yii\db\mssql\Schema',    // older MSSQL driver on MS Windows hosts
        'dblib' => 'yii\db\mssql\Schema',    // dblib drivers on GNU/Linux (and maybe other OSes) hosts
        'cubrid' => 'yii\db\cubrid\Schema',  // CUBRID
    ];
    /**
     * @var string Custom PDO wrapper class. If not set, it will use "PDO" or "yii\db\mssql\PDO" when MSSQL is used.
     */
    public $pdoClass;
    /**
     * @var boolean whether to enable [savepoint](http://en.wikipedia.org/wiki/Savepoint).
     * Note that if the underlying DBMS does not support savepoint, setting this property to be true will have no effect.
     */
    public $enableSavepoint = true;
    /**
     * @var Transaction the currently active transaction
     */
    private $_transaction;
    /**
     * @var Schema the database schema
     */
    private $_schema;
Carsten Brandt committed
264

265 266 267 268 269 270 271 272
    /**
     * Returns a value indicating whether the DB connection is established.
     * @return boolean whether the DB connection is established
     */
    public function getIsActive()
    {
        return $this->pdo !== null;
    }
273

274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290
    /**
     * Turns on query caching.
     * This method is provided as a shortcut to setting two properties that are related
     * with query caching: [[queryCacheDuration]] and [[queryCacheDependency]].
     * @param integer                 $duration   the number of seconds that query results may remain valid in cache.
     *                                            If not set, it will use the value of [[queryCacheDuration]]. See [[queryCacheDuration]] for more details.
     * @param \yii\caching\Dependency $dependency the dependency for the cached query result.
     *                                            See [[queryCacheDependency]] for more details.
     */
    public function beginCache($duration = null, $dependency = null)
    {
        $this->enableQueryCache = true;
        if ($duration !== null) {
            $this->queryCacheDuration = $duration;
        }
        $this->queryCacheDependency = $dependency;
    }
w  
Qiang Xue committed
291

292 293 294 295 296 297 298
    /**
     * Turns off query caching.
     */
    public function endCache()
    {
        $this->enableQueryCache = false;
    }
299

300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323
    /**
     * Establishes a DB connection.
     * It does nothing if a DB connection has already been established.
     * @throws Exception if connection fails
     */
    public function open()
    {
        if ($this->pdo === null) {
            if (empty($this->dsn)) {
                throw new InvalidConfigException('Connection::dsn cannot be empty.');
            }
            $token = 'Opening DB connection: ' . $this->dsn;
            try {
                Yii::trace($token, __METHOD__);
                Yii::beginProfile($token, __METHOD__);
                $this->pdo = $this->createPdoInstance();
                $this->initConnection();
                Yii::endProfile($token, __METHOD__);
            } catch (\PDOException $e) {
                Yii::endProfile($token, __METHOD__);
                throw new Exception($e->getMessage(), $e->errorInfo, (int) $e->getCode(), $e);
            }
        }
    }
w  
Qiang Xue committed
324

325 326 327 328 329 330 331 332 333 334 335 336 337
    /**
     * Closes the currently active DB connection.
     * It does nothing if the connection is already closed.
     */
    public function close()
    {
        if ($this->pdo !== null) {
            Yii::trace('Closing DB connection: ' . $this->dsn, __METHOD__);
            $this->pdo = null;
            $this->_schema = null;
            $this->_transaction = null;
        }
    }
w  
Qiang Xue committed
338

339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357
    /**
     * Creates the PDO instance.
     * This method is called by [[open]] to establish a DB connection.
     * The default implementation will create a PHP PDO instance.
     * You may override this method if the default PDO needs to be adapted for certain DBMS.
     * @return PDO the pdo instance
     */
    protected function createPdoInstance()
    {
        $pdoClass = $this->pdoClass;
        if ($pdoClass === null) {
            $pdoClass = 'PDO';
            if (($pos = strpos($this->dsn, ':')) !== false) {
                $driver = strtolower(substr($this->dsn, 0, $pos));
                if ($driver === 'mssql' || $driver === 'dblib' || $driver === 'sqlsrv') {
                    $pdoClass = 'yii\db\mssql\PDO';
                }
            }
        }
w  
Qiang Xue committed
358

359 360
        return new $pdoClass($this->dsn, $this->username, $this->password, $this->attributes);
    }
361

362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379
    /**
     * Initializes the DB connection.
     * This method is invoked right after the DB connection is established.
     * The default implementation turns on `PDO::ATTR_EMULATE_PREPARES`
     * if [[emulatePrepare]] is true, and sets the database [[charset]] if it is not empty.
     * It then triggers an [[EVENT_AFTER_OPEN]] event.
     */
    protected function initConnection()
    {
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        if ($this->emulatePrepare !== null && constant('PDO::ATTR_EMULATE_PREPARES')) {
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->emulatePrepare);
        }
        if ($this->charset !== null && in_array($this->getDriverName(), ['pgsql', 'mysql', 'mysqli', 'cubrid'])) {
            $this->pdo->exec('SET NAMES ' . $this->pdo->quote($this->charset));
        }
        $this->trigger(self::EVENT_AFTER_OPEN);
    }
w  
Qiang Xue committed
380

381 382 383 384 385 386 387 388 389 390 391 392 393
    /**
     * Creates a command for execution.
     * @param  string  $sql    the SQL statement to be executed
     * @param  array   $params the parameters to be bound to the SQL statement
     * @return Command the DB command
     */
    public function createCommand($sql = null, $params = [])
    {
        $this->open();
        $command = new Command([
            'db' => $this,
            'sql' => $sql,
        ]);
w  
Qiang Xue committed
394

395 396
        return $command->bindValues($params);
    }
w  
Qiang Xue committed
397

398 399 400 401 402 403 404 405
    /**
     * Returns the currently active transaction.
     * @return Transaction the currently active transaction. Null if no active transaction.
     */
    public function getTransaction()
    {
        return $this->_transaction && $this->_transaction->getIsActive() ? $this->_transaction : null;
    }
w  
Qiang Xue committed
406

407 408 409 410 411 412 413
    /**
     * Starts a transaction.
     * @return Transaction the transaction initiated
     */
    public function beginTransaction()
    {
        $this->open();
414

415 416 417 418
        if (($transaction = $this->getTransaction()) === null) {
            $transaction = $this->_transaction = new Transaction(['db' => $this]);
        }
        $transaction->begin();
w  
Qiang Xue committed
419

420 421
        return $transaction;
    }
w  
Qiang Xue committed
422

423 424 425 426 427 428 429 430 431 432 433 434 435 436
    /**
     * Returns the schema information for the database opened by this connection.
     * @return Schema                the schema information for the database opened by this connection.
     * @throws NotSupportedException if there is no support for the current driver type
     */
    public function getSchema()
    {
        if ($this->_schema !== null) {
            return $this->_schema;
        } else {
            $driver = $this->getDriverName();
            if (isset($this->schemaMap[$driver])) {
                $config = !is_array($this->schemaMap[$driver]) ? ['class' => $this->schemaMap[$driver]] : $this->schemaMap[$driver];
                $config['db'] = $this;
w  
Qiang Xue committed
437

438 439 440 441 442 443
                return $this->_schema = Yii::createObject($config);
            } else {
                throw new NotSupportedException("Connection does not support reading schema information for '$driver' DBMS.");
            }
        }
    }
Qiang Xue committed
444

445 446 447 448 449 450 451 452
    /**
     * Returns the query builder for the current DB connection.
     * @return QueryBuilder the query builder for the current DB connection.
     */
    public function getQueryBuilder()
    {
        return $this->getSchema()->getQueryBuilder();
    }
w  
Qiang Xue committed
453

454 455 456 457 458 459 460 461 462 463
    /**
     * Obtains the schema information for the named table.
     * @param  string      $name    table name.
     * @param  boolean     $refresh whether to reload the table schema even if it is found in the cache.
     * @return TableSchema table schema information. Null if the named table does not exist.
     */
    public function getTableSchema($name, $refresh = false)
    {
        return $this->getSchema()->getTableSchema($name, $refresh);
    }
w  
Qiang Xue committed
464

465 466 467 468 469 470 471 472 473 474
    /**
     * Returns the ID of the last inserted row or sequence value.
     * @param  string $sequenceName name of the sequence object (required by some DBMS)
     * @return string the row ID of the last row inserted, or the last value retrieved from the sequence object
     * @see http://www.php.net/manual/en/function.PDO-lastInsertId.php
     */
    public function getLastInsertID($sequenceName = '')
    {
        return $this->getSchema()->getLastInsertID($sequenceName);
    }
w  
Qiang Xue committed
475

476 477 478 479 480 481 482 483 484 485 486
    /**
     * Quotes a string value for use in a query.
     * Note that if the parameter is not a string, it will be returned without change.
     * @param  string $str string to be quoted
     * @return string the properly quoted string
     * @see http://www.php.net/manual/en/function.PDO-quote.php
     */
    public function quoteValue($str)
    {
        return $this->getSchema()->quoteValue($str);
    }
Qiang Xue committed
487

488 489 490 491 492 493 494 495 496 497 498 499
    /**
     * Quotes a table name for use in a query.
     * If the table name contains schema prefix, the prefix will also be properly quoted.
     * If the table name is already quoted or contains special characters including '(', '[[' and '{{',
     * then this method will do nothing.
     * @param  string $name table name
     * @return string the properly quoted table name
     */
    public function quoteTableName($name)
    {
        return $this->getSchema()->quoteTableName($name);
    }
500

501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548
    /**
     * Quotes a column name for use in a query.
     * If the column name contains prefix, the prefix will also be properly quoted.
     * If the column name is already quoted or contains special characters including '(', '[[' and '{{',
     * then this method will do nothing.
     * @param  string $name column name
     * @return string the properly quoted column name
     */
    public function quoteColumnName($name)
    {
        return $this->getSchema()->quoteColumnName($name);
    }

    /**
     * Processes a SQL statement by quoting table and column names that are enclosed within double brackets.
     * Tokens enclosed within double curly brackets are treated as table names, while
     * tokens enclosed within double square brackets are column names. They will be quoted accordingly.
     * Also, the percentage character "%" at the beginning or ending of a table name will be replaced
     * with [[tablePrefix]].
     * @param  string $sql the SQL to be quoted
     * @return string the quoted SQL
     */
    public function quoteSql($sql)
    {
        return preg_replace_callback('/(\\{\\{(%?[\w\-\. ]+%?)\\}\\}|\\[\\[([\w\-\. ]+)\\]\\])/',
            function ($matches) {
                if (isset($matches[3])) {
                    return $this->quoteColumnName($matches[3]);
                } else {
                    return str_replace('%', $this->tablePrefix, $this->quoteTableName($matches[2]));
                }
            }, $sql);
    }

    /**
     * Returns the name of the DB driver for the current [[dsn]].
     * @return string name of the DB driver
     */
    public function getDriverName()
    {
        if (($pos = strpos($this->dsn, ':')) !== false) {
            return strtolower(substr($this->dsn, 0, $pos));
        } else {
            $this->open();

            return strtolower($this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME));
        }
    }
w  
Qiang Xue committed
549
}