PHP Class yii\db\Connection

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). Connection supports database replication and read-write splitting. In particular, a Connection component can be configured with multiple [[masters]] and [[slaves]]. It will do load balancing and failover by choosing appropriate servers. It will also automatically direct read operations to the slaves and write operations to the masters. To establish a DB connection, set [[dsn]], [[username]] and [[password]], and then call Connection::open to be true. The following example shows how to create a Connection instance and establish the DB connection: ~~~ $connection = new \yii\db\Connection([ 'dsn' => $dsn, 'username' => $username, 'password' => $password, ]); $connection->open(); ~~~ After the DB connection is established, one can execute SQL statements like the following: ~~~ $command = $connection->createCommand('SELECT * FROM post'); $posts = $command->queryAll(); $command = $connection->createCommand('UPDATE post SET status=1'); $command->execute(); ~~~ 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: ~~~ $command = $connection->createCommand('SELECT * FROM post WHERE id=:id'); $command->bindValue(':id', $_GET['id']); $post = $command->query(); ~~~ 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: ~~~ $transaction = $connection->beginTransaction(); try { $connection->createCommand($sql1)->execute(); $connection->createCommand($sql2)->execute(); ... executing other SQL statements ... $transaction->commit(); } catch (Exception $e) { $transaction->rollBack(); } ~~~ You also can use shortcut for the above like the following: ~~~ $connection->transaction(function() { $order = new Order($customer); $order->save(); $order->addItems($items); }); ~~~ If needed you can pass transaction isolation level as a second parameter: ~~~ $connection->transaction(function(Connection $db) { return $db->... }, Transaction::READ_UNCOMMITTED); ~~~ Connection is often used as an application component and configured in the application configuration like the following: ~~~ 'components' => [ 'db' => [ 'class' => '\yii\db\Connection', 'dsn' => 'mysql:host=127.0.0.1;dbname=demo', 'username' => 'root', 'password' => '', 'charset' => 'utf8', ], ], ~~~
Since: 2.0
Author: Qiang Xue ([email protected])
Inheritance: extends yii\base\Component
Mostra file Open project: yiisoft/yii2 Class Usage Examples

Public Properties

Property Type Description
$attributes PDO attributes (name => value) that should be set when calling Connection::open to establish a DB connection. Please refer to the PHP manual for details about available attributes.
$charset 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 configured by the database. For Oracle Database, the charset must be specified in the [[dsn]], for example for UTF-8 by appending ;charset=UTF-8 to the DSN string. The same applies for if you're using GBK or BIG5 charset with MySQL, then it's highly recommended to specify charset via [[dsn]] like 'mysql:dbname=mydatabase;host=127.0.0.1;charset=GBK;'.
$commandClass the class used to create new database Command objects. If you want to extend the Command class, you may configure this property to use your extended version of the class.
$dsn the Data Source Name, or DSN, contains the information required to connect to the database. Please refer to the PHP manual on the format of the DSN string. For SQLite you may use a path alias for specifying the database path, e.g. sqlite:@app/data/db.sql.
$emulatePrepare 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.
$enableQueryCache 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. Also, only the results of the queries enclosed within Connection::cache will be cached.
$enableSavepoint whether to enable savepoint. Note that if the underlying DBMS does not support savepoint, setting this property to be true will have no effect.
$enableSchemaCache 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.
$enableSlaves whether to enable read/write splitting by using [[slaves]] to read data. Note that if [[slaves]] is empty, read/write splitting will NOT be enabled no matter what value this property takes.
$masterConfig the configuration that should be merged with every master configuration listed in [[masters]]. For example, php [ 'username' => 'master', 'password' => 'master', 'attributes' => [ use a smaller connection timeout PDO::ATTR_TIMEOUT => 10, ], ]
$masters list of master connection configurations. Each configuration is used to create a master DB connection. When Connection::open is called, one of these configurations will be chosen and used to create a DB connection which will be used by this object. Note that when this property is not empty, the connection setting (e.g. "dsn", "username") of this object will be ignored.
$password the password for establishing DB connection. Defaults to null meaning no password to use.
$pdo the PHP PDO instance associated with this DB connection. This property is mainly managed by Connection::open and Connection::close methods. When a DB connection is active, this property will represent a PDO instance; otherwise, it will be null.
$pdoClass Custom PDO wrapper class. If not set, it will use [[PDO]] or PDO when MSSQL is used.
$queryCache the cache object or the ID of the cache application component that is used for query caching.
$queryCacheDuration the default 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. The value of this property will be used when Connection::cache is called without a cache duration.
$schemaCache the cache object or the ID of the cache application component that is used to cache the table metadata.
$schemaCacheDuration number of seconds that table metadata can remain valid in cache. Use 0 to indicate that the cached data will never expire.
$schemaCacheExclude 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.
$schemaMap 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 Connection::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.
$serverRetryInterval the retry interval in seconds for dead servers listed in [[masters]] and [[slaves]]. This is used together with [[serverStatusCache]].
$serverStatusCache the cache object or the ID of the cache application component that is used to store the health status of the DB servers specified in [[masters]] and [[slaves]]. This is used only when read/write splitting is enabled or [[masters]] is not empty.
$slaveConfig the configuration that should be merged with every slave configuration listed in [[slaves]]. For example, php [ 'username' => 'slave', 'password' => 'slave', 'attributes' => [ use a smaller connection timeout PDO::ATTR_TIMEOUT => 10, ], ]
$slaves list of slave connection configurations. Each configuration is used to create a slave DB connection. When [[enableSlaves]] is true, one of these configurations will be chosen and used to create a DB connection for performing read queries only.
$tablePrefix 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}}.
$username the username for establishing DB connection. Defaults to null meaning no username to use.

Public Methods

Method Description
__sleep ( ) : array Close the connection before serializing.
beginTransaction ( string | null $isolationLevel = null ) : Transaction Starts a transaction.
cache ( callable $callable, integer $duration = null, yii\caching\Dependency $dependency = null ) : mixed Uses query cache for the queries performed with the callable.
close ( ) Closes the currently active DB connection.
createCommand ( string $sql = null, array $params = [] ) : Command Creates a command for execution.
getDriverName ( ) : string Returns the name of the DB driver. Based on the the current [[dsn]], in case it was not set explicitly by an end user.
getIsActive ( ) : boolean Returns a value indicating whether the DB connection is established.
getLastInsertID ( string $sequenceName = '' ) : string Returns the ID of the last inserted row or sequence value.
getMasterPdo ( ) : PDO Returns the PDO instance for the currently active master connection.
getQueryBuilder ( ) : QueryBuilder Returns the query builder for the current DB connection.
getQueryCacheInfo ( integer $duration, yii\caching\Dependency $dependency ) : array Returns the current query cache information.
getSchema ( ) : Schema Returns the schema information for the database opened by this connection.
getSlave ( boolean $fallbackToMaster = true ) : Connection Returns the currently active slave connection.
getSlavePdo ( boolean $fallbackToMaster = true ) : PDO Returns the PDO instance for the currently active slave connection.
getTableSchema ( string $name, boolean $refresh = false ) : yii\db\TableSchema Obtains the schema information for the named table.
getTransaction ( ) : Transaction Returns the currently active transaction.
noCache ( callable $callable ) : mixed Disables query cache temporarily.
open ( ) Establishes a DB connection.
quoteColumnName ( string $name ) : string Quotes a column name for use in a query.
quoteSql ( string $sql ) : string Processes a SQL statement by quoting table and column names that are enclosed within double brackets.
quoteTableName ( string $name ) : string Quotes a table name for use in a query.
quoteValue ( string $value ) : string Quotes a string value for use in a query.
setDriverName ( string $driverName ) Changes the current driver name.
transaction ( callable $callback, string | null $isolationLevel = null ) : mixed Executes callback provided in a transaction.
useMaster ( callable $callback ) : mixed Executes the provided callback by using the master connection.

Protected Methods

Method Description
createPdoInstance ( ) : PDO Creates the PDO instance.
initConnection ( ) Initializes the DB connection.
openFromPool ( array $pool, array $sharedConfig ) : Connection Opens the connection to a server in the pool.

Method Details

__sleep() public method

Close the connection before serializing.
public __sleep ( ) : array
return array

beginTransaction() public method

Starts a transaction.
public beginTransaction ( string | null $isolationLevel = null ) : Transaction
$isolationLevel string | null The isolation level to use for this transaction. See [[Transaction::begin()]] for details.
return Transaction the transaction initiated

cache() public method

When query caching is enabled ([[enableQueryCache]] is true and [[queryCache]] refers to a valid cache), queries performed within the callable will be cached and their results will be fetched from cache if available. For example, php The customer will be fetched from cache if available. If not, the query will be made against DB and cached for use next time. $customer = $db->cache(function (Connection $db) { return $db->createCommand('SELECT * FROM customer WHERE id=1')->queryOne(); }); Note that query cache is only meaningful for queries that return results. For queries performed with [[Command::execute()]], query cache will not be used.
See also: enableQueryCache
See also: queryCache
See also: noCache()
public cache ( callable $callable, integer $duration = null, yii\caching\Dependency $dependency = null ) : mixed
$callable callable a PHP callable that contains DB queries which will make use of query cache. The signature of the callable is `function (Connection $db)`.
$duration integer the number of seconds that query results can remain valid in the cache. If this is not set, the value of [[queryCacheDuration]] will be used instead. Use 0 to indicate that the cached data will never expire.
$dependency yii\caching\Dependency the cache dependency associated with the cached query results.
return mixed the return result of the callable

close() public method

It does nothing if the connection is already closed.
public close ( )

createCommand() public method

Creates a command for execution.
public createCommand ( string $sql = null, array $params = [] ) : Command
$sql string the SQL statement to be executed
$params array the parameters to be bound to the SQL statement
return Command the DB command

createPdoInstance() protected method

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.
protected createPdoInstance ( ) : PDO
return PDO the pdo instance

getDriverName() public method

Returns the name of the DB driver. Based on the the current [[dsn]], in case it was not set explicitly by an end user.
public getDriverName ( ) : string
return string name of the DB driver

getIsActive() public method

Returns a value indicating whether the DB connection is established.
public getIsActive ( ) : boolean
return boolean whether the DB connection is established

getLastInsertID() public method

Returns the ID of the last inserted row or sequence value.
See also: http://www.php.net/manual/en/function.PDO-lastInsertId.php
public getLastInsertID ( string $sequenceName = '' ) : string
$sequenceName string 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

getMasterPdo() public method

This method will open the master DB connection and then return [[pdo]].
public getMasterPdo ( ) : PDO
return PDO the PDO instance for the currently active master connection.

getQueryBuilder() public method

Returns the query builder for the current DB connection.
public getQueryBuilder ( ) : QueryBuilder
return QueryBuilder the query builder for the current DB connection.

getQueryCacheInfo() public method

This method is used internally by Command.
public getQueryCacheInfo ( integer $duration, yii\caching\Dependency $dependency ) : array
$duration integer the preferred caching duration. If null, it will be ignored.
$dependency yii\caching\Dependency the preferred caching dependency. If null, it will be ignored.
return array the current query cache information, or null if query cache is not enabled.

getSchema() public method

Returns the schema information for the database opened by this connection.
public getSchema ( ) : Schema
return Schema the schema information for the database opened by this connection.

getSlave() public method

If this method is called the first time, it will try to open a slave connection when [[enableSlaves]] is true.
public getSlave ( boolean $fallbackToMaster = true ) : Connection
$fallbackToMaster boolean whether to return a master connection in case there is no slave connection available.
return Connection the currently active slave connection. Null is returned if there is slave available and `$fallbackToMaster` is false.

getSlavePdo() public method

When [[enableSlaves]] is true, one of the slaves will be used for read queries, and its PDO instance will be returned by this method.
public getSlavePdo ( boolean $fallbackToMaster = true ) : PDO
$fallbackToMaster boolean whether to return a master PDO in case none of the slave connections is available.
return PDO the PDO instance for the currently active slave connection. Null is returned if no slave connection is available and `$fallbackToMaster` is false.

getTableSchema() public method

Obtains the schema information for the named table.
public getTableSchema ( string $name, boolean $refresh = false ) : yii\db\TableSchema
$name string table name.
$refresh boolean whether to reload the table schema even if it is found in the cache.
return yii\db\TableSchema table schema information. Null if the named table does not exist.

getTransaction() public method

Returns the currently active transaction.
public getTransaction ( ) : Transaction
return Transaction the currently active transaction. Null if no active transaction.

initConnection() protected method

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 initConnection ( )

noCache() public method

Queries performed within the callable will not use query cache at all. For example, php $db->cache(function (Connection $db) { ... queries that use query cache ... return $db->noCache(function (Connection $db) { this query will not use query cache return $db->createCommand('SELECT * FROM customer WHERE id=1')->queryOne(); }); });
See also: enableQueryCache
See also: queryCache
See also: cache()
public noCache ( callable $callable ) : mixed
$callable callable a PHP callable that contains DB queries which should not use query cache. The signature of the callable is `function (Connection $db)`.
return mixed the return result of the callable

open() public method

It does nothing if a DB connection has already been established.
public open ( )

openFromPool() protected method

This method implements the load balancing among the given list of the servers.
protected openFromPool ( array $pool, array $sharedConfig ) : Connection
$pool array the list of connection configurations in the server pool
$sharedConfig array the configuration common to those given in `$pool`.
return Connection the opened DB connection, or null if no server is available

quoteColumnName() public method

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.
public quoteColumnName ( string $name ) : string
$name string column name
return string the properly quoted column name

quoteSql() public method

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]].
public quoteSql ( string $sql ) : string
$sql string the SQL to be quoted
return string the quoted SQL

quoteTableName() public method

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.
public quoteTableName ( string $name ) : string
$name string table name
return string the properly quoted table name

quoteValue() public method

Note that if the parameter is not a string, it will be returned without change.
See also: http://www.php.net/manual/en/function.PDO-quote.php
public quoteValue ( string $value ) : string
$value string string to be quoted
return string the properly quoted string

setDriverName() public method

Changes the current driver name.
public setDriverName ( string $driverName )
$driverName string name of the DB driver

transaction() public method

Executes callback provided in a transaction.
public transaction ( callable $callback, string | null $isolationLevel = null ) : mixed
$callback callable a valid PHP callback that performs the job. Accepts connection instance as parameter.
$isolationLevel string | null The isolation level to use for this transaction. See [[Transaction::begin()]] for details.
return mixed result of callback function

useMaster() public method

This method is provided so that you can temporarily force using the master connection to perform DB operations even if they are read queries. For example, php $result = $db->useMaster(function ($db) { return $db->createCommand('SELECT * FROM user LIMIT 1')->queryOne(); });
public useMaster ( callable $callback ) : mixed
$callback callable a PHP callable to be executed by this method. Its signature is `function (Connection $db)`. Its return value will be returned by this method.
return mixed the return value of the callback

Property Details

$attributes public_oe property

PDO attributes (name => value) that should be set when calling Connection::open to establish a DB connection. Please refer to the PHP manual for details about available attributes.
public $attributes

$charset public_oe property

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 configured by the database. For Oracle Database, the charset must be specified in the [[dsn]], for example for UTF-8 by appending ;charset=UTF-8 to the DSN string. The same applies for if you're using GBK or BIG5 charset with MySQL, then it's highly recommended to specify charset via [[dsn]] like 'mysql:dbname=mydatabase;host=127.0.0.1;charset=GBK;'.
public $charset

$commandClass public_oe property

the class used to create new database Command objects. If you want to extend the Command class, you may configure this property to use your extended version of the class.
See also: createCommand
Since: 2.0.7
public $commandClass

$dsn public_oe property

the Data Source Name, or DSN, contains the information required to connect to the database. Please refer to the PHP manual on the format of the DSN string. For SQLite you may use a path alias for specifying the database path, e.g. sqlite:@app/data/db.sql.
See also: charset
public $dsn

$emulatePrepare public_oe property

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

$enableQueryCache public_oe property

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. Also, only the results of the queries enclosed within Connection::cache will be cached.
See also: queryCache
See also: cache()
See also: noCache()
public $enableQueryCache

$enableSavepoint public_oe property

whether to enable savepoint. Note that if the underlying DBMS does not support savepoint, setting this property to be true will have no effect.
public $enableSavepoint

$enableSchemaCache public_oe property

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 also: schemaCacheDuration
See also: schemaCacheExclude
See also: schemaCache
public $enableSchemaCache

$enableSlaves public_oe property

whether to enable read/write splitting by using [[slaves]] to read data. Note that if [[slaves]] is empty, read/write splitting will NOT be enabled no matter what value this property takes.
public $enableSlaves

$masterConfig public_oe property

the configuration that should be merged with every master configuration listed in [[masters]]. For example, php [ 'username' => 'master', 'password' => 'master', 'attributes' => [ use a smaller connection timeout PDO::ATTR_TIMEOUT => 10, ], ]
public $masterConfig

$masters public_oe property

list of master connection configurations. Each configuration is used to create a master DB connection. When Connection::open is called, one of these configurations will be chosen and used to create a DB connection which will be used by this object. Note that when this property is not empty, the connection setting (e.g. "dsn", "username") of this object will be ignored.
See also: masterConfig
public $masters

$password public_oe property

the password for establishing DB connection. Defaults to null meaning no password to use.
public $password

$pdo public_oe property

the PHP PDO instance associated with this DB connection. This property is mainly managed by Connection::open and Connection::close methods. When a DB connection is active, this property will represent a PDO instance; otherwise, it will be null.
See also: pdoClass
public $pdo

$pdoClass public_oe property

Custom PDO wrapper class. If not set, it will use [[PDO]] or PDO when MSSQL is used.
See also: pdo
public $pdoClass

$queryCache public_oe property

the cache object or the ID of the cache application component that is used for query caching.
See also: enableQueryCache
public $queryCache

$queryCacheDuration public_oe property

the default 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. The value of this property will be used when Connection::cache is called without a cache duration.
See also: enableQueryCache
See also: cache()
public $queryCacheDuration

$schemaCache public_oe property

the cache object or the ID of the cache application component that is used to cache the table metadata.
See also: enableSchemaCache
public $schemaCache

$schemaCacheDuration public_oe property

number of seconds that table metadata can remain valid in cache. Use 0 to indicate that the cached data will never expire.
See also: enableSchemaCache
public $schemaCacheDuration

$schemaCacheExclude public_oe property

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 also: enableSchemaCache
public $schemaCacheExclude

$schemaMap public_oe property

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 Connection::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

$serverRetryInterval public_oe property

the retry interval in seconds for dead servers listed in [[masters]] and [[slaves]]. This is used together with [[serverStatusCache]].
public $serverRetryInterval

$serverStatusCache public_oe property

the cache object or the ID of the cache application component that is used to store the health status of the DB servers specified in [[masters]] and [[slaves]]. This is used only when read/write splitting is enabled or [[masters]] is not empty.
public $serverStatusCache

$slaveConfig public_oe property

the configuration that should be merged with every slave configuration listed in [[slaves]]. For example, php [ 'username' => 'slave', 'password' => 'slave', 'attributes' => [ use a smaller connection timeout PDO::ATTR_TIMEOUT => 10, ], ]
public $slaveConfig

$slaves public_oe property

list of slave connection configurations. Each configuration is used to create a slave DB connection. When [[enableSlaves]] is true, one of these configurations will be chosen and used to create a DB connection for performing read queries only.
See also: enableSlaves
See also: slaveConfig
public $slaves

$tablePrefix public_oe property

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

$username public_oe property

the username for establishing DB connection. Defaults to null meaning no username to use.
public $username