PHP's YII framework uses database configuration and SQL Operations Example Tutorial _php instance

Database access (DAO)
YII contains a data access layer (DAO) built on top of the PHP PDO. DAO provides a unified set of APIs for different databases. Where ActiveRecord provides interaction between the database and the model (M,model in MVC), QueryBuilder is used to create dynamic query statements. DAO provides simple and efficient SQL queries that can be used in various places that interact with the database.

YII supports the following databases (DBMS) by default:

• Mysql
• MariaDB
• Sqlite
• PostgreSQL
• Cubrid: Version >= 9.3. (Because a bug reference value for PHP PDO extension is not valid, you need to use 9.3 on both the client and server side of the Cubrid)
• Oracle
• MSSQL: Version >=2005.

Configuration

Getting started with a database requires first configuring the database Connectivity component by adding a DB component to the application configuration implementation (the "underlying" Web application is config/web.php), and the DSN (data source name) is the data source name used to specify the database information. The following is shown:

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 ',//database user name      ' password ' + ',//Database Password ' charset ' + '      utf8 ',    ],< c17/>],  //...];

Please refer to PHP Manual for more information about DSN format. After you configure a connection component, you can access it using the following syntax:

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

Please refer to yii\db\connection for a list of configurable properties. If you want to connect to the database through ODBC, you need to configure Yii\db\connection::d Rivername property, for example:

' db ' = = [  ' class ' = ' yii\db\connection ',  ' drivername ' = ' mysql ',  ' dsn ' = ' + ' odbc:driver={ MySQL}; Server=localhost;database=test ',  ' username ' + ' root ',  ' password ' = ', ', ',

Note: If you need to use multiple databases at the same time, you can define multiple connection components:

return [  //...  ] Components ' = + [    //...    ' db ' = = [      ' class ' = ' yii\db\connection ',      ' dsn ' = ' mysql:host=localhost;dbname=mydatabase ',       ' Username ' + ' root ', ' password ' + ' ', ' charset ' and '      UTF8 ',    ],    ' seconddb ' = [      ' Class ' = ' yii\db\connection ',      ' dsn ' = ' sqlite:/path/to/database/file ',     ],  ],  //...];

Used in code in the following ways:

$primaryConnection = \yii:: $app->db; $secondaryConnection = \yii:: $app->seconddb;

If you do not want to define a database connection as a global application component, you can initialize it directly in your code:

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

Tip: If you need to perform additional SQL queries after creating a connection, you can add the following code to the application configuration file:

return [  //...  ] Components ' = + [    //...    ' db ' = = [      ' class ' = ' yii\db\connection ',      //...      ' On Afteropen ' = function ($event) {        $event->sender->createcommand ("SET time_zone = ' UTC ')->execute ( );      }    ],  ],  // ...];

If execute SQL does not return any data, you can use the Execute method in the command:

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

You can use the Insert,update,delete method, which generates the appropriate SQL and executes according to the parameters.

Insert$connection->createcommand ()->insert (' User ', [  ' name ' = ' Sam ',  ' age ' = ', ']) Execute ();//Insert multiple lines $connection->createcommand ()->batchinsert (' User ', [' name ', ' age '], [  [' Tom ', 30] ,  [' Jane ', +],  [' Linda ', ' +],])->execute ();//Update$connection->createcommand ()->update (' user ' , [' Status ' = 1], ' Age > ')->execute ();//Delete$connection->createcommand ()->delete (' User ', ' status = 0 ')->execute ();

Referenced table and column names

The following syntax is used most of the time to safely reference table and column names:

$sql = "Select COUNT ($column) from {{table}}"; $rowCount = $connection->createcommand ($sql)->queryscalar ();

The above code $column will be converted to refer to the appropriate column name, and {{table}} will be converted to a reference to the appropriate table name. The table name has a special variable {{%Y}}, and if you set the table prefix you can automatically prefix the table name with the variant:

$sql = "Select COUNT ($column) from {{% $table}}"; $rowCount = $connection->createcommand ($sql)->queryscalar ();

If the table prefix is set as follows in the configuration file, the above code will query the result in the Tbl_table table:

return [  //...  ] Components ' = + [    //...    ' db ' = = [      //...      ' Tableprefix ' = ' tbl_ ',],],  ;

Another option for manually referencing table and column names is to use Yii\db\connection::quotetablename () and Yii\db\connection::quotecolumnname ():

$column = $connection->quotecolumnname ($column), $table = $connection->quotetablename ($table); $sql = "Select COUNT ($column) from $table "; $rowCount = $connection->createcommand ($sql)->queryscalar ();

Preprocessing statements

To pass a query parameter for security, you can use a preprocessing statement, first using: Placeholder placeholder, and then binding the variable to the corresponding placeholder:

$command = $connection->createcommand (' SELECT * from post WHERE id=:id '); $command->bindvalue (': Id ', $_get[' id ']) ; $post = $command->query ();

Another use is to prepare a preprocessing statement and execute multiple queries:

$command = $connection->createcommand (' DELETE from post WHERE id=:id '), $command->bindparam (': Id ', $id); $id = 1;$ Command->execute (); $id = 2; $command->execute ();

Hint that binding a variable before execution and then changing the value of the variable in each execution (typically used in a loop) is more efficient.
Transaction

When you need to execute multiple related query sequences sequentially, you can encapsulate them in a transaction to protect data consistency. YII provides a simple interface to implement transactional operations. Execute the SQL transaction query statement as follows:

$transaction = $connection->begintransaction (); try {  $connection->createcommand ($sql 1)->execute ();   $connection->createcommand ($sql 2)->execute ();  // ... Execute other SQL statements  ... $transaction->commit ();} catch (Exception $e) {  $transaction->rollback ();}

We start a transaction by Yii\db\connection::begintransaction () and catch the exception through a try catch. When execution succeeds, the transaction is committed through Yii\db\transaction::commit () and ends, The transaction is rolled back by Yii\db\transaction::rollback () when an exception failure occurs.

Multiple transactions can also be nested if required:

External transaction $transaction1 = $connection->begintransaction (); try {  $connection->createcommand ($sql 1) Execute ();  Internal transaction  $transaction 2 = $connection->begintransaction ();  try {    $connection->createcommand ($sql 2)->execute ();    $transaction 2->commit ();  } catch (Exception $e) {    $transaction 2->rollback ();  }  $transaction 1->commit ();} catch (Exception $e) {  $transaction 1->rollback ();}

Note that the database you are using must support savepoints to execute correctly, the above code can be executed in all relational data, but security is guaranteed only with support for savepoints.
YII also supports setting isolation levels for transactions isolation levels, which uses the default isolation level of the database when executing transactions, and you can also specify isolation levels for things. YII provides the following constants as a common isolation level

• \yii\db\transaction::read_uncommitted-allows reading of changed uncommitted data, which can result in dirty reads, non-repeatable reads, and Phantom reads
• \yii\db\transaction::read_committed-Allows concurrent transactions to be read after committing, avoiding dirty reads, which can result in repeated reads and Phantom reads.
• \yii\db\transaction::repeatable_read-Multiple read results for the same field are consistent and can cause phantom reads.
• \yii\db\transaction::serializable-the principle of full compliance with acid ensures that no dirty reads, non-repeatable reads, and phantom reads occur.

You can use the constants above or use a string string command to execute the command in the corresponding database to set the isolation level, such as for Postgres valid command for serializable READ only deferrable.

Note: Some databases can only set the transaction isolation level for connections, so you must explicitly establish an isolation level for the connection. Currently affected database: MSSQL SQLite

Note: SQLite supports only two transaction isolation levels, so you can only set read UNCOMMITTED and SERIALIZABLE. Using other isolation levels throws an exception.

Note: PostgreSQL does not allow you to set the isolation level before the transaction starts, so you cannot specify the isolation level at the beginning of the transaction. You can set it by calling Yii\db\transaction::setisolationlevel () after the transaction begins.
About isolation level [isolation levels]: Http://en.wikipedia.org/wiki/Isolation_ (database_systems) #Isolation_levels

database replication and read-write separation

Many databases support database replication Http://en.wikipedia.org/wiki/Replication_ (computing) #Database_replication ">database Replication to improve availability and responsiveness. In database replication, data is always from the primary server to the slave server. All insert and update write operations are performed on the primary server, while read operations are performed from the server.

database replication and read-write separation can be achieved by configuring Yii\db\connection.

[  ' class ' = ' yii\db\connection ',  //Configure Master server  ' DSN ' = ' DSN for master server ',  ' username ' = ' Master ',  ' password ' = ' + ',  //config from server  ' slaveconfig ' and ' [    ' username ' = ' slave ',    ' Password ' = = ',    ' attributes ' = = [      //Use a smaller connection timeout      pdo::attr_timeout = 10,
  ],  ],  //config from server group  ' slaves ' = [    [' DSN ' = ' DSN for slave server 1 '],    [' DSN ' + ' DSN fo R slave server 2 '],    [' dsn ' = = ' DSN for slave server 3 '],    [' DSN ' + ' DSN for slave server 4 '],  ]

The above configuration realizes a main multi-slave structure, from the server to execute the read query, the master server performs the write query, the function of read and write separation is completed automatically by the background code. Callers need not care. For example:

Create a database connection object using the above configuration $db = Yii::createobject ($config);//By performing a query operation from the server $rows = $db->createcommand (' SELECT * from user LIMIT ')->queryall ()//The update operation is performed via the master server $db->createcommand ("Update user SET username= ' demo ' WHERE id=1 ') Execute ();

Note: Queries executed through Yii\db\command::execute () are considered to be write operations, and all other query methods that are executed using Yii\db\command are considered read operations. You can pass the $db-> Slave Gets the slave server that is currently being used.
The connection component supports load balancing and failover from the server, and when a read query is executed for the first time, a connection is selected from the server, and if the connection fails, the other is selected, and if all from the server is unavailable, the primary server is connected. You can configure Yii\db\connection::serverstatuscache to remember those that cannot connect from the server to make Yii over a period of time [[Yii\db\connection::serverretryinterval]. Does not repeatedly attempt to connect to the slave servers that are not available at all.

Note: In the above configuration, each slave server connection timeout is specified as 10s. If the connection cannot be made within 10s, the server is considered dead. You can also customize the timeout parameter.
You can also configure a multi-master multi-slave structure, for example:

[  ' class ' = ' yii\db\connection ',  //Configure the master server  ' masterconfig ' = [    ' username ' + ' master ',    ' Password ' = ' and ',    ' attributes ' = = [      //Use a smaller connection timeout      pdo::attr_timeout = 10,
  
   ],  ],  //Configure the Primary server group  ' masters ' = [    [' DSN ' = ' DSN for master server 1 '],    [' DSN ' = ' DSN ' for Master server 2 '],  ],  //config from server  ' slaveconfig ' = [    ' username ' = ' slave ',    ' password ' =& Gt ',    ' attributes ' = = [      //Use a smaller connection timeout      pdo::attr_timeout =],  ],  //config from server group  ' slaves ' = [' dsn ' = ' DSN    for slave server 1 '],    [' DSN ' = ' DSN ' for slave serve R 2 '],    [' dsn ' = = ' DSN for slave server 3 '],    [' DSN ' + ' DSN for slave server 4 '],  ]
  

The above configuration developed 2 master servers and 4 slave servers. The connection component also supports load balancing and failover of the primary server, and, unlike from the server, throws an exception if all primary servers are unavailable.

Note: When you use Yii\db\connection::masters to configure one or more primary servers, other properties related to database connections in Connection (for example: dsn,username, password) are ignored.
The transaction uses the primary server's connection by default, and all operations in the transaction will use the primary server's connection, for example:

Start transaction on primary server connection $transaction = $db->begintransaction (); try {  //All queries are executed on the primary server  $rows = $db CreateCommand (' SELECT * from User LIMIT ')->queryall ();  $db->createcommand ("UPDATE User SET username= ' demo ' WHERE id=1")->execute ();  $transaction->commit ();} catch (\exception $e) {  $transaction->rollback ();  Throw $e;}

If you want to perform transactional operations from the server, you must explicitly specify, for example:

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

Sometimes you want to force a master server to execute a read query, and you can call the Semaster () method.

$rows = $db->usemaster (function ($db) {  return $db->createcommand (' SELECT * from User LIMIT ')->queryall ( );});

You can also set $db->enableslaves to False to make all queries execute on the primary server.

• Manipulating Database schemas
• Obtaining schema information

You can get schema information by Yii\db\schema instances:

$schema = $connection->getschema ();

This example includes a series of methods to retrieve information about the various aspects of the database:

$tables = $schema->gettablenames ();

For more information, please refer to Yii\db\schema

Modify Mode

In addition to the underlying SQL queries, Yii\db\command also includes a series of methods to modify the database schema:

• Create/rename/delete/empty tables
• Add/rename/delete/modify fields
• Add/Remove Primary key
• Add/Remove foreign keys
• Create/Delete Index

Examples of Use:

CREATE Table $connection->createcommand ()->createtable (' Post ', [  ' id ' = ' pk ',  ' title ' = ' String ',  ' text ' = ' text ',]);

For a complete reference, please see Yii\db\command.

Examples of SQL queries:

Find the customers whose primary key value is 10$customers = Customer::findall (ten); $customer = Customer::findone (10);// The above code is equivalent to: $customers = Customer::find ()->where ([' id ' =])->all ();//Find the Customers Whose primary key value is ten, one or $customers = Customer::findall ([Ten, One,]); $customers = Customer::find ()->whe Re ([' in ', ' id ', [10,11,12]])->all ();//The above code is equivalent to: $customers = Customer::find ()->where ([' id ' =  > [Ten, One, one]])->all ();//Find customers whose age is the and whose status is 1$customers = Customer::findall ([' Age ' = +, ' 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 ' = +, ' StatuS ' + 1])->all ();//Get Customers Count$count = Customer::find ()->where ([' age ' = ' = ', ' status ' = 1])->c Ount ();//Add addition condition$customers = Customer::find ()->where ([' age ' = ' = ', ' status ' = 1])->andwhere (' Score > ')->orderby (' id DESC ')->offset (5)->limit ()->all ();//Find by Sql$customers = Customer:: Findbysql (' SELECT * from the customer WHERE age=30 and Status=1 and score>100 ORDER by ID DESC LIMIT 5,10 ')->all ();

Modify:

Update status for Customer-10$customer = Customer::findone (ten); $customer->status = 1; $customer->update ();// The above code is equivalent To:customer::updateall ([' status ' = = 1], ' id =: id ', [': Id ' =>10]);

Delete:

Delete Customer-10customer::findone ()->delete ()//The above code is equivalent To:customer::d eleteall ([' Status ' = 1], ' id =: id ', [': Id ' =>10]);

--------------------------------Use subqueries------------------------------------------

$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 ');

--------------------------------handwritten SQL-------------------------------------------

Select$customers = Yii:: $app->db->createcommand (' SELECT * from Customer ')->queryall ();//Updateyii:: $app- >db->createcommand ()->update (' Customer ', [' status ' =>1], ' id=10 ')->execute ();//Deleteyii:: $app- >db->createcommand ()->delete (' Customer ', ' id=10 ')->execute ();//transaction//outer $transaction 1 = $ Connection->begintransaction (); try {  $connection->createcommand ($sql 1)->execute ();  Internal  $transaction 2 = $connection->begintransaction ();  try {    $connection->createcommand ($sql 2)->execute ();    $transaction 2->commit ();  } catch (Exception $e) {    $transaction 2->rollback ();  }  $transaction 1->commit ();} catch (Exception $e) {  $transaction 1->rollback ();}

-----------------------------Master-Slave configuration--------------------------------------------

[  ' class ' = ' yii\db\connection ',  //Master   ' DSN ' = ' DSN for master server ',  ' username ' = ' Master ',  ' password ' = ' and ',  //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 '],  ],

