PHPのYiiフレームワークでのデータベースの構成とSQL操作のインスタンスチュートリアルの使用

データベースアクセス(DAO)Yiiは、PHP PDO上に構築するデータアクセス層(DAO)を含む.DAOは異なるデータベースに統一的なAPIを提供した.ここでActiveRecordはデータベースとモデル(MVC中のM,Model)とのインタラクションを提供し、QueryBuilderは動的なクエリ文を作成するために使用する.DAOは簡単で効率的なSQLクエリーを提供し、データベースとインタラクティブな場所で使用することができる.
Yiiのデフォルトでは、次のデータベース(DBMS)がサポートされています.

MySQL

MariaDB

SQLite

PostgreSQL

CUBRID:バージョン>=9.3.(PHP PDO拡張のバグ参照値が無効になるため、CUBRIDのクライアントとサービス側で9.3を使用する必要があります)

Oracle

MSSQL:バージョン>=2005.

コンフィギュレーション
データベースの使用を開始するには、まずデータベース接続コンポーネントを構成する必要があり、dbコンポーネントをアプリケーション構成に追加することによって(「基本的な」Webアプリケーションはconfig/web.php)、DSN(Data Source Name)はデータベース情報を指定するためのデータソース名である.次のようになります.

return [
  // ...
  'components' => [
    // ...
    'db' => [
      'class' => 'yii\db\Connection',
      'dsn' => 'mysql:host=localhost;dbname=mydatabase', // MySQL, MariaDB
      //'dsn' => 'sqlite:/path/to/database/file', // SQLite
      //'dsn' => 'pgsql:host=localhost;port=5432;dbname=mydatabase', // PostgreSQL
      //'dsn' => 'cubrid:dbname=demodb;host=localhost;port=33000', // CUBRID
      //'dsn' => 'sqlsrv:Server=localhost;Database=mydatabase', // MS SQL Server, sqlsrv driver
      //'dsn' => 'dblib:host=localhost;dbname=mydatabase', // MS SQL Server, dblib driver
      //'dsn' => 'mssql:host=localhost;dbname=mydatabase', // MS SQL Server, mssql driver
      //'dsn' => 'oci:dbname=//localhost:1521/mydatabase', // Oracle
      'username' => 'root', //      
      'password' => '', //     
      'charset' => 'utf8',
    ],
  ],
  // ...
];

DSNフォーマットの詳細については、PHP manualを参照してください.接続コンポーネントを構成すると、次の構文を使用してアクセスできます.

$connection = \Yii::$app->db;

yiidbConnectionを参照して、構成可能なプロパティのリストを取得してください.ODBCでデータベースに接続する場合は、yiidbConnection::driverNameプロパティ(例:

'db' => [
  'class' => 'yii\db\Connection',
  'driverName' => 'mysql',
  'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=test',
  'username' => 'root',
  'password' => '',
],

注:複数のデータベースを同時に使用する必要がある場合は、複数の接続コンポーネントを定義します.

return [
  // ...
  'components' => [
    // ...
    'db' => [
      'class' => 'yii\db\Connection',
      'dsn' => 'mysql:host=localhost;dbname=mydatabase', 
      'username' => 'root',
      'password' => '',
      'charset' => 'utf8',
    ],
    'secondDb' => [
      'class' => 'yii\db\Connection',
      'dsn' => 'sqlite:/path/to/database/file', 
    ],
  ],
  // ...
];

コードでは、次の方法で使用します.

$primaryConnection = \Yii::$app->db;
$secondaryConnection = \Yii::$app->secondDb;

データベース接続をグローバルアプリケーションコンポーネントとして定義したくない場合は、コードで直接初期化して使用できます.

$connection = new \yii\db\Connection([
  'dsn' => $dsn,
   'username' => $username,
   'password' => $password,
]);
$connection->open();

ヒント:接続を作成した後、追加のSQLクエリーを実行する必要がある場合は、次のコードをアプリケーションプロファイルに追加できます.

return [
  // ...
  'components' => [
    // ...
    'db' => [
      'class' => 'yii\db\Connection',
      // ...
      'on afterOpen' => function($event) {
        $event->sender->createCommand("SET time_zone = 'UTC'")->execute();
      }
    ],
  ],
  // ...
];

SQLを実行してデータを返さない場合は、コマンドのexecuteメソッドを使用します.

$command = $connection->createCommand('UPDATE post SET status=1 WHERE id=1');
$command->execute();

パラメータに基づいて適切なSQLを生成して実行するinsert,update,deleteメソッドを使用できます.

// INSERT
$connection->createCommand()->insert('user', [
  'name' => 'Sam',
  'age' => 30,
])->execute();

// INSERT       
$connection->createCommand()->batchInsert('user', ['name', 'age'], [
  ['Tom', 30],
  ['Jane', 20],
  ['Linda', 25],
])->execute();

// UPDATE
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

// DELETE
$connection->createCommand()->delete('user', 'status = 0')->execute();

参照されるテーブル名とカラム名
ほとんどの場合、次の構文を使用して、テーブル名とカラム名を安全に参照します.

$sql = "SELECT COUNT($column) FROM {{table}}";
$rowCount = $connection->createCommand($sql)->queryScalar();

上記のコード$columnは適切なカラム名を参照することに変わり、{{table}}は適切なテーブル名を参照することに変わります.テーブル名には特殊な変数{%Y}}があり、このバリエーションを使用してテーブル名に接頭辞を自動的に追加できます.

$sql = "SELECT COUNT($column) FROM {{%$table}}";
$rowCount = $connection->createCommand($sql)->queryScalar();

プロファイルに次のようにテーブル接頭辞が設定されている場合、上記のコードはtbl_になります.tableこのテーブルのクエリー結果:

return [
  // ...
  'components' => [
    // ...
    'db' => [
      // ...
      'tablePrefix' => 'tbl_',
    ],
  ],
];

テーブル名とカラム名を手動で参照するもう1つの選択肢は、yiidbConnection::quoteTable Name()とyiidbConnection::quoteColumnName():

$column = $connection->quoteColumnName($column);
$table = $connection->quoteTableName($table);
$sql = "SELECT COUNT($column) FROM $table";
$rowCount = $connection->createCommand($sql)->queryScalar();

プリプロセッシングステートメント
クエリー・パラメータを安全に渡すには、前処理文を使用します.まず、placeholderを使用して、変数を対応するプレースホルダにバインドします.

$command = $connection->createCommand('SELECT * FROM post WHERE id=:id');
$command->bindValue(':id', $_GET['id']);
$post = $command->query();

もう1つの方法は、1回の前処理文を準備して複数のクエリーを実行することです.

$command = $connection->createCommand('DELETE FROM post WHERE id=:id');
$command->bindParam(':id', $id);

$id = 1;
$command->execute();

$id = 2;
$command->execute();

なお、実行前に変数をバインド、実行ごとに変数の値を変更する(一般的にループで使用される)ことが効率的である.取引
複数の関連queryを順番に実行する必要がある場合は、1つのトランザクションにカプセル化してデータの一貫性を保護することができます.Yiiは、トランザクション動作を実現するための簡単なインタフェースを提供する.SQLトランザクションクエリ文は次のように実行されます.

$transaction = $connection->beginTransaction();
try {
  $connection->createCommand($sql1)->execute();
   $connection->createCommand($sql2)->execute();
  // ...      SQL    ...
  $transaction->commit();
} catch(Exception $e) {
  $transaction->rollBack();
}

yiidbConnection::beginTransaction()でトランザクションを開始し、try catchで例外を取得します.実行に成功すると、yiidbTransaction::commit()でトランザクションをコミットして終了し、異常な失敗が発生するとyiidbTransaction::rollBack()でトランザクションをロールバックする.
必要に応じて、複数のトランザクションをネストすることもできます.

//     
$transaction1 = $connection->beginTransaction();
try {
  $connection->createCommand($sql1)->execute();

  //     
  $transaction2 = $connection->beginTransaction();
  try {
    $connection->createCommand($sql2)->execute();
    $transaction2->commit();
  } catch (Exception $e) {
    $transaction2->rollBack();
  }

  $transaction1->commit();
} catch (Exception $e) {
  $transaction1->rollBack();
}

これらのコードは、すべてのリレーショナル・データで実行できますが、Savepointsをサポートしてこそセキュリティを保証することができます.Yiiはトランザクションに独立性レベルisolation levelsを設定することもサポートし、トランザクションを実行するときにデータベースのデフォルトの独立性レベルを使用し、物事に独立性レベルを指定することもできます.Yiiは、一般的な分離レベルとして以下の定数を提供する

\yii\db\Transaction::READ_UNCOMMITTED-変更されたコミットされていないデータの読み取りが許可され、汚れた読み取り、重複しない読み取り、幻の読み取りが発生する可能性があります.

\yii\db\Transaction::READ_COMMITTED-同時トランザクションのコミット後の読み取りを許可し、汚れた読み取りを回避し、重複読み取りと幻読みを引き起こす可能性があります.

\yii\db\Transaction::REPEATABLE_READ-同じフィールドに対する複数回の読み取り結果が一致し、幻読みを引き起こす可能性がある.

yiidbTransaction::SERIALIZABLE-ACIDの原則に完全に従い、汚れた読み取り、重複しない読み取り、幻の読み取りが発生しないことを確保します.

以上の定数を使用するか、string文字列コマンドを使用して、postgresに対して有効なコマンドがSERIALIZABLE READ ONLY DEFERRABLEのような分離レベルを設定するために、対応するデータベースで実行することができます.
注意:一部のデータベースでは、接続に対してのみトランザクションの独立性レベルを設定できるため、接続の独立性レベルを明確に設定する必要があります.現在影響を受けているデータベース:MSSQL SQLite
注意:SQLiteは2つのトランザクション分離レベルしかサポートしていないので、READ UNCOMMITTEDとSERIALIZABLEしか設定できません.他の独立性レベルを使用すると例外が放出されます.
注意:PostgreSQLでは、トランザクションの開始前に独立性レベルを設定できません.したがって、トランザクションの開始時に独立性レベルを指定することはできません.トランザクションの開始後にyiidbTransaction::setisolationLevel()を呼び出して設定できます.独立性レベル[isolation levels]:http://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_levels
データベースのコピーと読み書きの分離
多くのデータベースでは、データベース・レプリケーションがサポートされています.http://en.wikipedia.org/wiki/Replication_(computing)#Database_Replication">database Replicationは、可用性と応答速度を向上するために使用する.データベースレプリケーションでは、データは常にプライマリサーバからセカンダリサーバへ.すべての挿入や更新などの書き込み操作はプライマリサーバで実行され、読み取り操作はサーバから実行する.
yiidbConnectionを構成することで、データベースのレプリケーションと読み書きの分離を実現できます.

[
  'class' => 'yii\db\Connection',

  //       
  'dsn' => 'dsn for master server',
  'username' => 'master',
  'password' => '',

  //       
  'slaveConfig' => [
    'username' => 'slave',
    'password' => '',
    'attributes' => [
      // use a smaller connection timeout
      PDO::ATTR_TIMEOUT => 10,
    ],
  ],

  //        
  'slaves' => [
    ['dsn' => 'dsn for slave server 1'],
    ['dsn' => 'dsn for slave server 2'],
    ['dsn' => 'dsn for slave server 3'],
    ['dsn' => 'dsn for slave server 4'],
  ],
]

以上の構成は一主多従の構造を実現し、サーバから読み取りクエリーを実行する、メインサーバは書き込みクエリーを実行し、読み書き分離の機能はバックグラウンドコードによって自動的に完成する.呼び出し者は関心を持つ必要はない.例:

//                
$db = Yii::createObject($config);

//             
$rows = $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();

//             
$db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

注意:yiidbCommand::execute()によって実行するクエリーは書き込み操作とみなされ、yiidbCommandを使用して実行する他のすべてのクエリー方法は読み取り操作とみなされる.$db->slaveで現在使用可能なスレーブサーバを得ることができます.Connectionコンポーネントは、サーバーからの負荷分散とフェイルオーバをサポートします.最初にリード・クエリーを実行すると、サーバーから接続するか、接続に失敗した場合は別を選択し、すべてのスレーブ・サーバーが使用できない場合はプライマリ・サーバーに接続します.yiidbConnection::serverStatusCacheを構成して接続できないスレーブサーバを覚え、Yiiをしばらくの間[[yiidbConnection::serverRetryInterval].内部では、まったく使用できないスレーブサーバへの接続を繰り返すことはありません.
なお、上記構成では、サーバ接続からのタイムアウト時間は10 sとする.10 s以内に接続することができなければ、そのサーバはすでに停止していると考えられる.タイムアウトパラメータをカスタマイズすることもできます.マルチプライマリマルチスレーブの構造を構成することもできます.たとえば、次のようにします.

[
  'class' => 'yii\db\Connection',

  //       
  'masterConfig' => [
    'username' => 'master',
    'password' => '',
    'attributes' => [
      // use a smaller connection timeout
      PDO::ATTR_TIMEOUT => 10,
    ],
  ],

  //        
  'masters' => [
    ['dsn' => 'dsn for master server 1'],
    ['dsn' => 'dsn for master server 2'],
  ],

  //       
  'slaveConfig' => [
    'username' => 'slave',
    'password' => '',
    'attributes' => [
      // use a smaller connection timeout
      PDO::ATTR_TIMEOUT => 10,
    ],
  ],

  //        
  'slaves' => [
    ['dsn' => 'dsn for slave server 1'],
    ['dsn' => 'dsn for slave server 2'],
    ['dsn' => 'dsn for slave server 3'],
    ['dsn' => 'dsn for slave server 4'],
  ],
]

上記構成は、2つのマスタサーバと4つのスレーブサーバとを定める.Connectionコンポーネントは、プライマリ・サーバの負荷分散とフェイルオーバもサポートする、スレーブ・サーバとは異なり、すべてのプライマリ・サーバが使用できない場合、例外が放出されます.
注意:yiidbConnection::mastersを使用して1つ以上のプライマリ・サーバを構成する場合、Connectionのデータベース接続に関する他のプロパティ(dsn、username、passwordなど)は無視されます.トランザクションのデフォルトでは、プライマリ・サーバの接続が使用され、トランザクション実行中のすべての操作でプライマリ・サーバの接続が使用されます.たとえば、次のようになります.

//             
$transaction = $db->beginTransaction();

try {
  //               
  $rows = $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
  $db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

  $transaction->commit();
} catch(\Exception $e) {
  $transaction->rollBack();
  throw $e;
}

サーバからトランザクションを実行するには、次のように明確に指定する必要があります.

$transaction = $db->slave->beginTransaction();

メインサーバを強制的に使用して読み取りクエリーを実行したい場合は、seMaster()メソッドを呼び出すことができます.

$rows = $db->useMaster(function ($db) {
  return $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
});

$db->enableSlavesをfalseに設定して、すべてのクエリーをプライマリ・サーバで実行することもできます.

操作データベースモード

取得モード情報

yiidbSchemaインスタンスからSchema情報を取得できます.

$schema = $connection->getSchema();

この例には、データベースのさまざまな情報を取得するための一連の方法が含まれます.

$tables = $schema->getTableNames();

詳細はyiidbSchemaを参照してください
モードの変更
ベースのSQLクエリに加えて、yiidbCommandには、データベース・モードを変更するための一連の方法が含まれています.

テーブルの作成/名前変更/削除/空にする

フィールドの追加/名前変更/削除/変更

プライマリ・キー

の追加/削除

外部キー

の追加/削除

インデックスの作成/削除

使用例:

//    
$connection->createCommand()->createTable('post', [
  'id' => 'pk',
  'title' => 'string',
  'text' => 'text',
]);

詳細については、yiidbCommand.を参照してください.
SQLクエリーの例:

// find the customers whose primary key value is 10
$customers = Customer::findAll(10);
$customer = Customer::findOne(10);

// the above code is equivalent to:
$customers = Customer::find()->where(['id' => 10])->all();

// find the customers whose primary key value is 10, 11 or 12.
$customers = Customer::findAll([10, 11, 12]);
$customers = Customer::find()->where(['IN','id',[10,11,12]])->all();

// the above code is equivalent to:
$customers = Customer::find()->where(['id' => [10, 11, 12]])->all();

// find customers whose age is 30 and whose status is 1
$customers = Customer::findAll(['age' => 30, 'status' => 1]);

// the above code is equivalent to:
$customers = Customer::find()->where(['age' => 30, 'status' => 1])->all();

// use params binding
$customers = Customer::find()->where('age=:age AND status=:status')->addParams([':age'=>30,':status'=>1])->all();

// use index
$customers = Customer::find()->indexBy('id')->where(['age' => 30, 'status' => 1])->all();

// get customers count
$count = Customer::find()->where(['age' => 30, 'status' => 1])->count();

// add addition condition
$customers = Customer::find()->where(['age' => 30, 'status' => 1])->andWhere('score > 100')->orderBy('id DESC')->offset(5)->limit(10)->all();

// find by sql
$customers = Customer::findBySql('SELECT * FROM customer WHERE age=30 AND status=1 AND score>100 ORDER BY id DESC LIMIT 5,10')->all();

変更:

// update status for customer-10
$customer = Customer::findOne(10);
$customer->status = 1;
$customer->update();

// the above code is equivalent to:
Customer::updateAll(['status' => 1], 'id = :id',[':id'=>10]);

削除:

// delete customer-10
Customer::findOne(10)->delete();

// the above code is equivalent to:
Customer::deleteAll(['status' => 1], 'id = :id',[':id'=>10]);

-------------------------------------------------------------------------------------------------------------

$subQuery = (new Query())->select('COUNT(*)')->from('customer');

// SELECT `id`, (SELECT COUNT(*) FROM `customer`) AS `count` FROM `customer`
$query = (new Query())->select(['id', 'count' => $subQuery])->from('customer');

------------------------------手書きSQL----------------------------------------------------

// select
$customers = Yii::$app->db->createCommand('SELECT * FROM customer')->queryAll();

// update
Yii::$app->db->createCommand()->update('customer',['status'=>1],'id=10')->execute();

// delete
Yii::$app->db->createCommand()->delete('customer','id=10')->execute();

//transaction
// outer 
$transaction1 = $connection->beginTransaction();
try {
  $connection->createCommand($sql1)->execute();

  // internal
  $transaction2 = $connection->beginTransaction();
  try {
    $connection->createCommand($sql2)->execute();
    $transaction2->commit();
  } catch (Exception $e) {
    $transaction2->rollBack();
  }

  $transaction1->commit();
} catch (Exception $e) {
  $transaction1->rollBack();
}

---------------------------------------------主従配置--------------------------------------------------------------------------------------------------------------------

[
  'class' => 'yii\db\Connection',

  // master 
  'dsn' => 'dsn for master server',
  'username' => 'master',
  'password' => '',

  // slaves
  'slaveConfig' => [
    'username' => 'slave',
    'password' => '',
    'attributes' => [
      // use a smaller connection timeout
      PDO::ATTR_TIMEOUT => 10,
    ],
  ],

  'slaves' => [
    ['dsn' => 'dsn for slave server 1'],
    ['dsn' => 'dsn for slave server 2'],
    ['dsn' => 'dsn for slave server 3'],
    ['dsn' => 'dsn for slave server 4'],
  ],
]