Database: Getting Started

Introduction

Swoft DB operations are highly compatible with Laravel ability to use native SQL , a smooth query constructor, and the Eloquent ORM to interact with the DB from now on, eliminating the complex object association model. Connect to the database using native PDO .

Say why this is the original way to adopt PDO this time.

Pdo and mysqli extensions using mysqlnd mode will add Hook listener. If mysqlnd is not enabled, it will not support coroutine.

Even if the IO operation is automatically converted and the swoole MySQL 协程客户端 . Make development simpler and closer to traditional frameworks.

Basic configuration

The configuration of the database is placed in the app\bean.php file, removing the cumbersome .env file configuration. You can think of the configured db as a bean object.

 return [
    'db'         => [
       'class'     => Database::class,
       'dsn'       => 'mysql:dbname=test;host=127.0.0.1',
       'username'  => 'root',
       'password'  => '123456',
       'charset'   => 'utf8mb4',
    ],
]; 

The configuration is similar to the configuration of the yii2 object attribute injection method. You can use the \bean('db') to get the currently configured Database object.

  • Class specifies which class the current bean container uses. Of course you can also specify your own implementation of the database class.
  • Connection configuration information required by dsn PDO
  • Username data login username
  • Password database login password
  • Charset database character set

Detailed configuration of attributes

Detailed configuration example:

 
'db'  => [
    'class'    => Database::class,
    'dsn'      => 'mysql:dbname=swoft;host=127.0.0.1',
    'username' => 'root',
    'password' => '123456',
    'charset'  => 'utf8mb4',
    'prefix'   => 't_',
    'options'  => [
        \PDO::ATTR_CASE => \PDO::CASE_NATURAL,
    ],
    'config'   => [
        'collation' => 'utf8mb4_general_ci',
        'strict'    => false,
        'timezone'  => '+8:00',
        'modes'     => 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES',
        'fetchMode' => PDO::FETCH_ASSOC,
    ],
], 
  • The public prefix or suffix of the prefix table name.
  • Options PDO property options
     /**
     * 默认的PDO连接选项。当然你可以选择替换它 
     *
     * @var array
     */
    $options = [
        \PDO::ATTR_CASE              => \PDO::CASE_NATURAL,
        \PDO::ATTR_ERRMODE           => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_ORACLE_NULLS      => \PDO::NULL_NATURAL,
        \PDO::ATTR_STRINGIFY_FETCHES => false,
        \PDO::ATTR_EMULATE_PREPARES  => false,
        \PDO::ATTR_ERRMODE           => \PDO::ERRMODE_EXCEPTION,
    ]; 
  • Config MySQL other configuration, the default driver only implements MySQL If you need other database startup, please refer to Connector & Connection.
    • Collation sets how the specified dataset is sorted.
    • Timezone set time zone settings
    • Modes set the connection mode (can be a one-dimensional array, or a comma-separated mode)
    • Strict setting gets the query to enable strict mode (actually also set the modes configuration)
    • fetchMode sets the type returned by pdo. The default connection pool returns the type FETCH_ASSOC or array mode. Other types refer to the official manual.

The default db.pool connection pool DB class query query operation returns an array. If the custom db connection pool also needs to return an array, you need to set the fetchMode parameter to PDO::FETCH_ASSOC , otherwise return the stdClass object.

Read & write & connect

 'db2'  => [
    'class'  => Database::class,
    'charset'  => 'utf8mb4',
    'prefix'   => 't_',
    'options'  => [],
    'config'   => [
       'collation' => 'utf8mb4_general_ci',
       'strict'    => false,
       'timezone'  => '+8:00',
       'modes'     => 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES',
    ],
    'writes' => [
        [
            'dsn'      => 'mysql:dbname=swoft;host=127.0.0.1',
            'username' => 'root',
            'password' => '123456',
        ],
    ],
    'reads'  => [
        [
            'dsn'      => 'mysql:dbname=swoft;host=127.0.0.1',
            'username' => 'root',
            'password' => '123456',
        ],
    ],
], 
  • Writes the main library configuration to perform the Create Update Delete operation will randomly select the main library node from the connection pool

  • Reads from the library configuration Read operation using the default will be from the connection pool Randomly select a library from the library node if you want to force the query, then use the useWritePdo() method in the chain operation

If you want a public configuration, please refer to the basic configuration . By default, the merged dsn' , 'username' ,'password','charset' ,'prefix' ,'options', 'config options are configured publicly, so configuring the changed values in writes/reads is writes/reads , you also Can override the upper configuration.

Connection pool configuration

The 1.0 partner that you must use is no stranger to the connection pool. 2.x has simplified the configuration of the connection pool. It does not need to say better management resources, database protection.

The connection of db is created and released through the 连接池 . The ConnectionManager class is used to manage the connection. The created short-link operation fails and will be retried once.

Whenever the toSql() method is called or the operation is completed, it will be returned to the connection pool. The default is that the connection pool name is db.pool . The database is \bean('db') which is the basic configuration configuration. The configuration is placed in the app\bean.php file.

Every `worker` will create the same connection pool. Not as many as possible, the parameter configuration is based on the machine configuration and the number of `worker`.

Let's see how the connection pool customizes a connection pool.

 'db.pool2' => [
    'class'       => \Swoft\Db\Pool::class,
    'database'    => \bean('db2'),
    'minActive'   => 10,
    'maxActive'   => 20,
    'maxWait'     => 0,
    'maxWaitTime' => 0,
    'maxIdleTime' => 60,
 ] 
  • Class is the default Pool object. You can inherit it from the official one and replace it with your own Pool class name.
  • Database driven database object is read & write connection configuration
  • The number of connections that the minActive connection pool needs to maintain.
  • maxActive connection pool maximum number of connections
  • maxWait connection pool waits for the maximum number of connections, if there is no limit to 0 (default)
  • maxWaitTime The maximum wait time for the connection, in seconds, if there is no limit to 0 (default)
  • maxIdleTime connection maximum idle time, in seconds

Connector&Connection

The relationship between the connector and the connection is necessary to create a connection

Connector

Connector mainly used to create a real PDO create a connection according to the configuration.

Connection

Connection mainly used for parsing the syntax of the database, setting the table prefix, obtaining the default query syntax instance, reconnecting the error judgment.

Swoft only provides MySQL's Connector&Connection by default. Because swoole temporarily adds PDO extensions such as pdo_pgsql,pdo_ori,pdo_odbc,pdo_firebird to the underlying Hook .

In other words pdo_pgsql,pdo_ori,pdo_odbc,pdo_firebird the IO operations executed by pdo_pgsql,pdo_ori,pdo_odbc,pdo_firebird will not let the CPU 资源 be executed synchronously . The 协程 will not 上下文切换 during execution .

If you want to use pgsql (you can use Swoole 's 协程PgSQL 客户端 ), you can just implement the Connector and Connection reference MySQL implementation.

Some insights about sub-database sub-tables

Swoft DB does not provide a library and sub-table solution. The dbSelect interface implementation interface is used to select different databases. Currently, you can select different databases by configuring the db.pool 连接池 and db 连接 . For details on switching connection pools, see: Selecting a connection pool.

Insights about removing DB service

Swoft 2.x removes the service discovery related stuff, and the integration into the framework is cumbersome. Subsequent dbProvide interface is provided to provide the connection address interface.

/docs/2.x/en/db/setting.html
progress-bar