Database: Getting Started

Brief introduction

Swoft DBThe highly compatible operation Laravel enables the use of native SQL , smooth query constructors, and from this to the Eloquent ORM DB interaction becomes simple, removing the complex object Association model. Connect the database in an native PDO way.

Tell me why PDO the original way to use this time

PDO, mysqli extensions that use Mysqlnd mode are added to Hook listening, and if MYSQLND is not enabled, the Federation threaded is not supported

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

Basic Configuration

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

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

Configuration is similar to the configuration of the yii2 object property injection method, which can be \bean('db') obtained by getting the currently configured Database object

  • CLASS specifies bean which class the current container uses of course you can also specify the classes that you implement database
  • PDOConnection configuration information that DSN needs to use
  • Username Data Login User name
  • Password Database login password
  • CharSet Database Character Set

Property configuration Details

Detailed Configuration examples:


'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.
  • PDOOptions Properties option
    /**
     * 默认的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,
    ];
  • MySQLOther configurations of config, the default driver is implemented only MySQL if additional database startup is required please Refer to Connector Connection
    • The collation setting specifies how the dataset is sorted.
    • TimeZone Set time zone settings
    • Modes sets the connection mode (which can be a one-dimensional array or an English comma-separated modes)
    • Strict settings get query to enable strict mode (and actually set modes configuration)
    • FetchMode set PDO returns the type of default connection pool returned by the type FETCH_ASSOC that is also the array way, other types refer to the official manual

The default db.pool connection pool DB class query query operation, which returns an array. Custom db Connection Pool If you also need to return an array, you need to set the fetchMode parameter to PDO::FETCH_ASSOC , otherwise the object is returned stdClass .

Read and write to the cable connection

'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',
        ],
    ],
],
  • When the writes Master library configuration performs Create Update Delete an operation, the primary library node is randomly selected from the connection pool to perform

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

If you want a public configuration, refer to the underlying configuration . The default overrides the merge of dsn' , 'username' ,'password','charset' ,'prefix' ,'options', 'config these options configuration common, so writes/reads you can configure the changed values in it, or you can override the upper configuration.

Connection Pool Configuration

Presumably 1.0 of the small partners are no stranger to the connection pool 2.x simplified configuration of it The benefits of connection pooling also do not have to say much better management resources, the protection of the database

DB connections are created 连接池 and released by the ConnectionManager class to manage the connection, and the creation of a short link operation failure will be retried once.

The toSql() connection to the connection pool is returned whenever the method is called or the operation is completed. The default is that db.pool the database \bean('db') connection pool name is used That is, the underlying configuration configuration, The connection pool configuration is placed in a app\bean.php file.

Each ' worker ' creates the same connection pool. Not the more the better, the parameter configuration should be measured according to the machine configuration and the number of ' worker '.

Let's look at how the connection pool can customize 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 more officially inherit the implementation, and then switch to their own Pool class name on it.
  • Database driven database objects are read in the _ _ write connection configuration
  • Number of connections required to maintain minActive connection pool
  • MaxActive Connection Pool Maximum number of connections maintained
  • MaxWait connection pool waits for a maximum number of connections, if not limited to 0 (default)
  • MaxWaitTime Connection maximum wait time, per unit of seconds, if not limited to 0 (default)
  • MaxIdleTime Connection Maximum idle time, unit seconds

Connector&Connection

The relationship between connectors and connections is necessary to create a connection

Connector

ConnectorMainly used to create a real create connection based on the configuration PDO

Connection

ConnectionMainly used for the syntax parsing of the database, setting the table prefix, getting the default query syntax instance, the re-connection error judgment

SwoftThe default is only provided Connector&Connection For MySQL for what? Because for swoole the pdo_pgsql,pdo_ori,pdo_odbc,pdo_firebird These PDO extensions join The ground floor Hook .

pdo_pgsql,pdo_ori,pdo_odbc,pdo_firebird IO That is, using the action performed will not CPU 资源 be conceded synchronously and 协程 will not be performed during execution上下文切换

If you want to use pgsql (you can use Swoole 协程 PgSQL 客户端 ) is also possible just implement Connector and refer To the Connection implementation of MySQL can be

Some insights on the sub-table

Swoft DB does not provide sub-libraries and sub-tables for subsequent scenarios dbSelect interface to provide implementation interfaces to select different database, For the time being, you can db.pool 连接池 db 连接 select different databases by configuring and selecting them. Toggle Connection Pool See: Select Connection Pool

Insights on removing DB service

Swoft 2.x removes the service discovery related things, fits into the frame appears bulky, follow-up will provide dbProvide interfaceTo provide access to the connection address interface

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