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',
],
],
~~~
/**
* @throws \yii\base\InvalidConfigException
* @throws \yii\db\Exception
*/
public function actionUpdate()
{
$assignments = $this->getAllAssignments();
$useTransaction = $this->authManager instanceof \yii\rbac\DbManager && $this->useTransaction === true;
$transaction = null;
if ($useTransaction) {
$transaction = $this->db->beginTransaction();
}
try {
$this->authManager->removeAll();
$this->updateRules();
$this->updateRoles();
$this->updatePermission();
$this->updateInheritanceRoles();
$this->updateInheritancePermissions();
if (!empty($assignments)) {
$this->restoreAssignments($assignments);
}
if ($transaction !== null) {
$transaction->commit();
}
} catch (\Exception $e) {
$this->stderr($e->getMessage() . PHP_EOL);
if ($transaction !== null) {
$transaction->rollBack();
}
}
if ($this->authManager instanceof \yii\rbac\DbManager) {
$this->authManager->invalidateCache();
}
}
/**
* @param boolean $reset whether to clean up the test database
* @param boolean $open whether to open and populate test database
* @return \yii\db\Connection
*/
public function getConnection($reset = true, $open = true)
{
if (!$reset && $this->db) {
return $this->db;
}
$db = new \yii\db\Connection();
$db->dsn = $this->database['dsn'];
if (isset($this->database['username'])) {
$db->username = $this->database['username'];
$db->password = $this->database['password'];
}
if (isset($this->database['attributes'])) {
$db->attributes = $this->database['attributes'];
}
if ($open) {
$db->open();
$lines = explode(';', file_get_contents($this->database['fixture']));
foreach ($lines as $line) {
if (trim($line) !== '') {
$db->pdo->exec($line);
}
}
}
$this->db = $db;
return $db;
}
/**
* @param boolean $reset whether to clean up the test database
* @param boolean $open whether to open and populate test database
* @throws \yii\base\InvalidParamException
* @throws \yii\db\Exception
* @throws \yii\base\InvalidConfigException
* @return \yii\db\Connection
*/
public function getConnection($reset = true, $open = true)
{
if (!$reset && $this->db) {
return $this->db;
}
$db = new Connection();
$db->dsn = $this->database['dsn'];
if (isset($this->database['username'])) {
$db->username = $this->database['username'];
$db->password = $this->database['password'];
}
if (isset($this->database['attributes'])) {
$db->attributes = $this->database['attributes'];
}
if ($open) {
$db->open();
$lines = explode(';', file_get_contents(\Yii::getAlias('@yii/rbac/schema-' . $this->driverName . '.sql')));
foreach ($lines as $line) {
if (trim($line) !== '') {
$db->pdo->exec($line);
}
}
}
$this->db = $db;
return $db;
}
public function addUser($ldap)
{
$profile = Yii::$app->userHelpers->getUserProfileFromHr($ldap->citizen);
$profile['moo'] = empty($profile['moo']) ? '' : ' หมู่ ' . $profile['moo'];
$profile['soi'] = empty($profile['soi']) ? '' : ' ซอย ' . $profile['soi'];
$profile['street'] = empty($profile['street']) ? '' : ' ถ.' . $profile['street'];
$profile['district'] = empty($profile['district']) ? '' : ' ต.' . $profile['district'];
$profile['amphur'] = empty($profile['amphur']) ? '' : ' อ.' . $profile['amphur'];
$profile['province'] = empty($profile['province']) ? '' : ' จ.' . $profile['province'];
$profile['zipcode'] = empty($profile['zipcode']) ? '' : ' ' . $profile['zipcode'];
$user = new User();
$user->id = $profile['id'];
$user->username = $this->username;
$user->email = "{$this->username}@kku.ac.th";
$user->password_hash = Password::hash($this->password);
$user->save(false);
$connection = new Connection(Yii::$app->db);
$connection->createCommand('INSERT INTO auth_assignment VALUES(:item_name, :user_id, :created_at)', [':item_name' => 'User', ':user_id' => $profile['id'], ':created_at' => time()])->execute();
$admins = Yii::$app->getModule('user')->admins;
if (in_array($ldap->username, $admins)) {
$connection->createCommand('INSERT INTO auth_assignment VALUES(:item_name, :user_id, :created_at)', [':item_name' => 'Administrator', ':user_id' => $profile['id'], ':created_at' => time()])->execute();
}
$connection->createCommand('UPDATE profile SET
name = :name,
address = :address,
phone = :phone,
faculty_id = :faculty_id,
position_id = :position_id,
position_type_id = :position_type_id,
level_id = :level_id,
division_id = :division_id
WHERE user_id = :user_id
', [':name' => "{$profile['title']}{$profile['firstname']} {$profile['lastname']}", ':address' => "{$profile['homeadd']}{$profile['moo']}{$profile['soi']}{$profile['street']}{$profile['district']}{$profile['amphur']}{$profile['province']}{$profile['zipcode']}", ':phone' => isset($profile['telephone']) ? $profile['telephone'] : null, ':faculty_id' => isset($profile['faculty_id']) ? $profile['faculty_id'] : Yii::$app->mappingHelpers->mapFaculty($profile['faculty'])['id'], ':position_id' => isset($profile['position_id']) ? $profile['position_id'] : Yii::$app->mappingHelpers->mapPosition($profile['posi'])['id'], ':position_type_id' => isset($profile['position_type_id']) ? $profile['position_type_id'] : Yii::$app->mappingHelpers->mapPositionType($profile['positype'])['id'], ':level_id' => isset($profile['level_id']) ? $profile['level_id'] : Yii::$app->mappingHelpers->mapLevel($profile['level'])['id'], ':division_id' => isset($profile['division_id']) ? $profile['division_id'] : Yii::$app->mappingHelpers->mapDivision($profile['division'])['id'], ':user_id' => $profile['id']])->execute();
}
public static function createAdminUser(AdminUser $model, \yii\db\Connection $db)
{
$db->createCommand()->insert('{{%user}}', ['username' => $model->username, 'password_hash' => Yii::$app->security->generatePasswordHash($model->password), 'email' => $model->email, 'auth_key' => '', 'create_time' => time(), 'update_time' => time()])->execute();
$userId = intval($db->lastInsertID);
$assignmentResult = $db->createCommand()->insert('{{%auth_assignment}}', ['item_name' => 'admin', 'user_id' => $userId])->execute() === 1;
return $assignmentResult && $userId > 0;
}
protected function tearDown()
{
if ($this->_db) {
$this->_db->close();
}
$this->destroyApplication();
}
public static function search(array $input)
{
$connection = new Connection(['dsn' => 'mysql:host=localhost;dbname=work', 'username' => 'root', 'password' => 'root', 'charset' => 'utf8']);
$connection->open();
/**запрос выбирает имя сети, агенства и сумму
* с группировкой ао агенству и "ИТОГО"
* из подзапроса суммы с границами по датам.
* COALESCE() меняет сумму NULL на 0
*/
$sql = 'SELECT agency_network.agency_network_name, agency.agency_name, COALESCE(sum(t.amount),0) AS sum
FROM agency_network
RIGHT JOIN agency
ON agency_network.agency_network_id = agency.agency_network_id
LEFT JOIN
(SELECT amount, date, agency_id
FROM billing WHERE date BETWEEN :startdate AND :enddate) t
ON t.agency_id=agency.agency_id
GROUP BY agency_name WITH ROLLUP;';
/*Привязываем параметры с датами, для исключения иньекций*/
$command = $connection->createCommand($sql)->bindParam(':startdate', $startdate)->bindParam(':enddate', $enddate);
$startdate = $input[date1];
$enddate = $input[date2];
$result = $command->queryAll();
$connection->close();
return $result;
}
/**
* @param Connection $db
* @param bool $refresh
* @return string[]
*/
public static function getSchemaNames(Connection $db, $refresh = false)
{
try {
$schemaNames = array_diff($db->getSchema()->getSchemaNames($refresh), ['public']);
} catch (NotSupportedException $e) {
$schemaNames = [];
}
return $schemaNames;
}
/**
* @return bool
*/
public function hasConnect()
{
$connection = new Connection(['dsn' => "mysql:host={$this->host};dbname={$this->dbname}", 'username' => $this->username, 'password' => $this->password, 'charset' => $this->charset]);
try {
$connection->open();
return true;
} catch (\Exception $e) {
return false;
}
}
/**
* Конфиг полученный из БД
* */
private function getConfigFromDb()
{
$dbConfig = $this->textConfig['components']['db'];
$connection = new Connection(['dsn' => $dbConfig['dsn'], 'username' => $dbConfig['username'], 'password' => $dbConfig['password']]);
$connection->open();
$config = $connection->createCommand('SELECT * FROM settings')->queryAll();
return ArrayHelper::map($config, 'name', 'value', function ($array) {
return ArrayHelper::getValue($array, 'category');
});
}
public function init()
{
parent::init();
$host = '127.0.0.1';
$dbname = 'tmc_test';
$connection = new Connection(['dsn' => 'mysql:host=' . $host . ';dbname=' . $dbname, 'username' => 'root', 'password' => '', 'charset' => 'utf8', 'tablePrefix' => 't_']);
$connection->open();
$this->db = $connection;
if ($this->cache !== null) {
$this->cache = Instance::ensure($this->cache, Cache::className());
}
}
protected function setUp()
{
parent::setUp();
$this->mockApplication();
Yii::$app->set('db', ['class' => Connection::className(), 'dsn' => 'sqlite::memory:']);
Yii::$app->db->createCommand()->createTable('session', ['id' => 'string', 'expire' => 'integer', 'data' => 'text', 'user_id' => 'integer'])->execute();
}
/**
* Builds and executes a SQL statement for dropping an index.
* @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
* @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
*/
public function dropIndex($name, $table)
{
echo " > drop index {$name} ...";
$time = microtime(true);
$this->db->createCommand()->dropIndex($name, $table)->execute();
echo " done (time: " . sprintf('%.3f', microtime(true) - $time) . "s)\n";
}
public function close()
{
if ($this->pdo !== null) {
$this->_transaction = null;
}
parent::close();
}
public function init()
{
parent::init();
$db = Instance::ensure($this->db, Connection::className());
$query = new Query();
$this->ticket = $query->select(['*'])->from($this->table)->createCommand($db)->queryAll();
}
public function testOpenClose()
{
$connection = $this->getConnection(false, false);
$this->assertFalse($connection->isActive);
$this->assertEquals(null, $connection->pdo);
$connection->open();
$this->assertTrue($connection->isActive);
$this->assertTrue($connection->pdo instanceof \PDO);
$connection->close();
$this->assertFalse($connection->isActive);
$this->assertEquals(null, $connection->pdo);
$connection = new Connection();
$connection->dsn = 'unknown::memory:';
$this->setExpectedException('yii\\db\\Exception');
$connection->open();
}
public function __construct($db = 'db')
{
$this->db = Instance::ensure($db, Connection::className());
$this->generator = new Generator();
$this->dbHelper = new Migration(['db' => $this->db]);
$this->generatorConfigurator = new GeneratorConfigurator();
}
public function init()
{
parent::init();
$this->i2db = Instance::ensure($this->i2db, Connection::className());
$this->infodb = Instance::ensure($this->infodb, Connection::className());
$this->db46 = Instance::ensure($this->db46, Connection::className());
}
/**
* Initializes the migration.
* This method will set [[db]] to be the 'db' application component, if it is `null`.
*/
public function init()
{
parent::init();
$this->db = Instance::ensure($this->db, Connection::className());
$this->db->getSchema()->refresh();
$this->db->enableSlaves = false;
}
/**
* Initializes the DB connection component.
* This method will initialize the [[db]] property to make sure it refers to a valid DB connection.
* @throws InvalidConfigException if [[db]] is invalid.
*/
public function init()
{
parent::init();
if (is_string($this->db)) {
$this->db = Instance::ensure($this->db, Connection::className());
}
}
/**
* @inheritdoc
*/
public static function getDb()
{
$host = '127.0.0.1';
$dbname = 'tmc_test';
$connection = new Connection(['dsn' => 'mysql:host=' . $host . ';dbname=' . $dbname, 'username' => 'root', 'password' => '', 'charset' => 'utf8', 'tablePrefix' => 't_']);
$connection->open();
return $connection;
//if (Configs::instance()->db !== null) {
// echo 'sdfdsf';
// exit;
// return Configs::instance()->db;
//} else {
// echo 'xxxxx';
// exit;
// return parent::getDb();
//}
}
/**
* Starts a transaction.
* @param string|null $isolationLevel The isolation level to use for this transaction.
* See [[Transaction::begin()]] for details.
* @return Transaction the transaction initiated
*/
public function beginTransaction($isolationLevel = null)
{
$transaction = parent::beginTransaction(null);
if ($isolationLevel !== null) {
$transaction->setIsolationLevel($isolationLevel);
}
return $transaction;
}
/**
* Initializes the DbMessageSource component.
* This method will initialize the [[db]] property to make sure it refers to a valid DB connection.
* Configured [[cache]] component would also be initialized.
* @throws InvalidConfigException if [[db]] is invalid or [[cache]] is invalid.
*/
public function init()
{
parent::init();
$this->db = Instance::ensure($this->db, Connection::className());
if ($this->enableCaching) {
$this->cache = Instance::ensure($this->cache, Cache::className());
}
}
/**
* @inheritdoc
*/
public function init()
{
parent::init();
$this->db = Instance::ensure($this->db, Connection::className());
if (is_string($this->cache)) {
$this->cache = Yii::$app->get($this->cache, false);
}
}
/**
* @inheritdoc
*/
public function beforeAction($action)
{
if (parent::beforeAction($action)) {
$this->db = Instance::ensure($this->db, Connection::className());
return true;
}
return false;
}
/**
* 批量插入订单的基本信息
* @param array $orderBaseArray
* @throws \yii\db\Exception
*/
public function insertOrderBase(array $orderBaseArray)
{
if (!empty($orderBaseArray)) {
$tableName = 't_oms_order';
$tableFields = [];
$tableValues = [];
foreach ($orderBaseArray as $orderBase) {
if (empty($tableFields)) {
$tableFields = array_keys($orderBase);
}
$tableValues[] = array_values($orderBase);
}
$transaction = $this->db->beginTransaction();
$this->db->createCommand()->batchInsert($tableName, $tableFields, $tableValues)->execute();
$transaction->commit();
}
}
public function exec($aliasPath)
{
$path = str_replace('/', DIRECTORY_SEPARATOR, \Yii::getAlias($aliasPath));
$this->migrationPath = dirname($path);
$this->db = Instance::ensure($this->db, Connection::className());
$this->getNewMigrations();
return $this->migrateUp(basename($path));
}
/**
* Populates Yii::$app with a new application
* The application will be destroyed on tearDown() automatically.
* @param array $config The application configuration, if needed
* @param string $appClass name of the application class to create
*/
protected function mockApplication($config = [], $appClass = '\\yii\\console\\Application')
{
new $appClass(ArrayHelper::merge(['id' => 'testapp', 'basePath' => __DIR__, 'vendorPath' => '../../vendor', 'controllerMap' => ['deferred' => ['class' => DeferredController::className()]], 'components' => ['mutex' => ['class' => 'yii\\mutex\\MysqlMutex', 'autoRelease' => false], 'db' => ['class' => Connection::className(), 'dsn' => 'mysql:host=localhost;dbname=yii2_deferred_tasks', 'username' => 'root', 'password' => ''], 'cache' => ['class' => 'yii\\caching\\FileCache']]], $config));
Yii::$app->cache->flush();
Yii::$app->getDb()->open();
Yii::$app->runAction('migrate/down', [99999, 'interactive' => 0, 'migrationPath' => __DIR__ . '/../src/migrations/']);
Yii::$app->runAction('migrate/up', ['interactive' => 0, 'migrationPath' => __DIR__ . '/../src/migrations/']);
}
protected function commit()
{
/* @fixme: implement batch operations */
foreach ($this->toRemove as $category => $keyvalue) {
foreach ($keyvalue as $key => $value) {
$this->db->createCommand()->delete($this->tableName, ['category' => $category, 'key' => $key])->execute();
}
}
foreach ($this->toSave as $category => $keyvalue) {
foreach ($keyvalue as $key => $value) {
$obj = (new Query())->from($this->tableName)->where(['category' => $category, 'key' => $key])->one($this->db);
$command = $this->db->createCommand();
if ($obj) {
$command = $command->update($this->tableName, ['value' => @serialize($value)], ['category' => $category, 'key' => $key]);
} else {
$command = $command->insert($this->tableName, ['category' => $category, 'key' => $key, 'value' => @serialize($value)]);
}
$command->execute();
}
}
if (count($this->toRemove) + count($this->toSave) > 0) {
$this->cache->delete('settings');
}
$this->toRemove = [];
$this->toSave = [];
}
public function init()
{
$this->db = Instance::ensure($this->db, Connection::className());
parent::init();
// Note the default configuration data value will not store to database.
$this->data = array_merge($this->loadData(), $this->data);
}