Tutorial on Database Configuration and SQL operations in the PHP Yii framework, and tutorial on yii instances

Tutorial on Database Configuration and SQL operations in the PHP Yii framework, and tutorial on yii instances

Database Access (DAO)
Yii contains a data access layer (DAO) built on php pdo ). DAO provides a unified set of APIS for different databases. activeRecord provides interaction between databases and models (M, Model in MVC). QueryBuilder is used to create dynamic query statements. DAO provides simple and efficient SQL queries, which can be used in various areas of interaction with databases.

By default, Yii supports the following databases (DBMS ):

• MySQL
• MariaDB
• SQLite
• PostgreSQL
• CUBRID: version> = 9.3. (because a bug reference value of the php pdo extension will be invalid, you need to use 9.3 on both the client and server of CUBRID)
• Oracle
• MSSQL: version> = 2005.

Configuration

To start using a database, you must first configure the database connection component and add the db component to the application configuration. (the "Basic" Web application is config/web. php), DSN (Data Source Name) is the Name of the Data Source, used to specify the database information. as follows

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',
    ],
  ],
  // ...
];
Please refer to PHP manual for more information about DSN format. After the connection component is configured, it can be accessed 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 via ODBC, you need to configure the yii \ db \ Connection :: driverName 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' => 'utf8',
    ],
    'secondDb' => [
      'class' => 'yii \ db \ Connection',
      'dsn' => 'sqlite: / path / to / database / file',
    ],
  ],
  // ...
];
Used in the code by:

$ primaryConnection = \ Yii :: $ app-> db;
$ secondaryConnection = \ Yii :: $ app-> secondDb;
If you do not want to define the 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 execute additional SQL queries after the connection is created, 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 executing 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 insert, update, delete methods, which will generate appropriate SQL based on the parameters and execute them.

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

// INSERT inserts multiple rows at once
$ 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 ();

Referenced table and column names

Most of the time, the following syntax is used 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 the appropriate column name, and {{table}} will be converted to the appropriate table name. There is a special variable {{% Y}} in the table name. If the table prefix is set, use this variant to automatically add a prefix before the table name:

$ sql = "SELECT COUNT ($ column) FROM {{% $ table}}";
$ rowCount = $ connection-> createCommand ($ sql)-> queryScalar ();
If the table prefix is set in the configuration file as follows, the above code will query the table in tbl_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 ();
Prepared statements

Prepared statements can be passed for safe transfer of query parameters. You should first use the: placeholder placeholder, and then bind the variable to the corresponding placeholder:

$ command = $ connection-> createCommand ('SELECT * FROM post WHERE id =: id');
$ command-> bindValue (': id', $ _GET ['id']);
$ post = $ command-> query ();
Another usage is to prepare a prepared 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, it is more efficient to bind variables before execution, and then change the value of the variable in each execution (usually used in loops).
Transaction

When you need to execute multiple related queries in sequence, you can encapsulate them into a transaction to protect data consistency. Yii provides a simple interface to implement transaction operations. Execute SQL transaction query statements as follows:

$ transaction = $ connection-> beginTransaction ();
try {
  $ connection-> createCommand ($ sql1)-> execute ();
   $ connection-> createCommand ($ sql2)-> execute ();
  // ... execute other SQL statements ...
  $ transaction-> commit ();
} catch (Exception $ e) {
  $ transaction-> rollBack ();
}
We start a transaction through yii \ db \ Connection :: beginTransaction (), and catch the exception through try catch. When the execution is successful, the transaction is submitted and ended through yii \ db \ Transaction :: commit (). When an exception fails, we pass yii \ db \ Transaction :: rollBack () performs transaction rollback.

You can also nest multiple transactions if needed:

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

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

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

Pay attention to what you use The database must support Savepoints to execute correctly. The above code can be executed in all relational data, but only support Savepoints to ensure security.
Yii also supports setting isolation levels for transactions. The database's default isolation level is used when executing transactions. You can also specify isolation levels for things. Yii provides the following constants as common isolation levels

\ yii \ db \ Transaction :: READ_UNCOMMITTED-allows reading of changed data that has not been submitted, which may result in dirty reads, non-repeatable reads, and phantom reads
\ yii \ db \ Transaction :: READ_COMMITTED-allows concurrent transactions to be read after committing, which can avoid dirty reads and may cause repeated reads and magic reads.
\ yii \ db \ Transaction :: REPEATABLE_READ-The result of multiple reads on the same field is consistent, which can lead to phantom reads.
\ yii \ db \ Transaction :: SERIALIZABLE-Fully obeys the ACID principle, ensuring that no dirty reads, non-repeatable reads, and phantom reads occur.
You can use the above constant or use a string command to execute the command in the corresponding database to set the isolation level. For example, the effective command for postgres is SERIALIZABLE READ ONLY DEFERRABLE.

Note: Some databases can only set the transaction isolation level for the connection, so you must clearly specify the isolation level for the connection. Currently affected databases: MSSQL SQLite

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

Note: PostgreSQL does not allow you to set the isolation level before the transaction starts, so you cannot specify the isolation level when the transaction starts. You can call yii \ db \ Transaction :: setIsolationLevel () after the transaction starts to set it.
About 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 response speed. In database replication, data is always from the master to the slave. The insert and update write operations are performed on the master server, and the read operations are performed on the slave server.

Database replication and read-write separation can be achieved by configuring yii \ db \ Connection.

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

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

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

  // Configure slave server group
  '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'],
  ],
]

The above configuration implements a master-multiple-slave structure. The slave server is used to perform read queries. The master server performs write queries. The read-write separation function is automatically completed by the background code. The caller does not need to care.

// 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 10')-> queryAll ();

// Perform the update operation through the main server
$ db-> createCommand ("UPDATE user SET username = 'demo' WHERE id = 1")-> execute ();

Note: The query executed by yii \ db \ Command :: execute () is considered to be a write operation, and all other query methods executed by yii \ db \ Command are considered to be read operations. You can get it through $ db-> slave You are currently using a capable slave server.
The Connection component supports load balancing and failover of the slave server. When a read query is performed for the first time, one slave server is selected for connection. If the connection fails, another is selected. If all slave servers are unavailable, they are connected. The main server. You can configure yii \ db \ Connection :: serverStatusCache to remember those slave servers that cannot be connected, so that Yii will not repeatedly try to connect to slaves that are not available at all for a period of time [[yii \ db \ Connection :: serverRetryInterval]. server.

Note: In the above configuration, the timeout time for each slave server connection is specified as 10s. If you cannot connect within 10s, it is considered that the server has hung up. You can also customize the timeout parameters.
You can also configure a multi-master multi-slave structure, for example:

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

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

  // Configure the main server group
  'masters' => [
    ['dsn' => 'dsn for master server 1'],
    ['dsn' => 'dsn for master server 2'],
  ],

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

  // Configure slave server group
  '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'],
  ],
]

The above configuration specifies 2 master servers and 4 slave servers. The Connection component also supports load balancing and failover of the master server. Unlike the slave servers, if all master servers are unavailable, an exception is thrown.

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

// Start transaction on the main server connection
$ transaction = $ db-> beginTransaction ();

try {
  // all queries are executed on the master server
  $ 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;
}

If you want to perform transaction operations on the slave server, you must specify it explicitly, such as:

$ transaction = $ db-> slave-> beginTransaction ();
Sometimes you want to force the master server to perform read queries, you can call the seMaster () method.

$ rows = $ db-> useMaster (function ($ db) {
  return $ db-> createCommand ('SELECT * FROM user LIMIT 10')-> queryAll ();
});
You can also set $ db-> enableSlaves to false to make all queries execute on the master server.

Operation database mode
Get model information
You can get Schema information through yii \ db \ Schema instance:

$ schema = $ connection-> getSchema ();
This example includes a series of methods to retrieve multiple aspects of the database:

$ tables = $ schema-> getTableNames ();
For more information, refer to yii \ db \ Schema

Modification mode

In addition to basic SQL queries, yii \ db \ Command also includes a series of methods to modify the database schema:

Create / Rename / Delete / Empty Table
Add / Rename / Delete / Modify fields
Add / Delete Primary Key
Add / remove foreign keys
Create / Delete Index
Example of use:

// create table
$ connection-> createCommand ()-> createTable ('post', [
  'id' => 'pk',
  'title' => 'string',
  'text' => 'text',
]);
For complete reference, please see yii \ db \ Command.

SQL query example:

// 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 ();

modify:

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

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

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

-------------------------------- Using 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 ();

// 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 ();
}

----------------------------- Master-slave configuration ------------------ --------------------------

[
  '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'],
  ],
]

Articles you may be interested in:
Method for removing behavior bound by component in PHP Yii framework
Explanation of behavior definition and binding method in PHP Yii framework
How to Use Behaviors in the Yii Framework in PHP
In-depth explanation of properties in PHP's Yii framework (Property)
In-depth analysis of the event mechanism in PHP's Yii framework
Comprehensive interpretation of the logging function in PHP's Yii framework
Yii uses find findAll to find out the implementation of the specified field
Analyze the addition, deletion, and modification of the Yii database
Yii PHP Framework Practical Getting Started Tutorial (Detailed Introduction)
Detailed explanation of attribute injection and method injection of component behavior in PHP's Yii framework