【改造背景】
使用Yii2.0开发api时，使用asArray()函数时，框架本身默认将所有数据类型都转换成字符串处理。在与前端强类型语言交互时极不方便。所以希望改造框架返回数据表对应字段类型，而无需每次手工进行转换。
【改造步骤】
1、数据库连接配置attributes中增加两个PDO参数：
return [ 'class' => 'commonextConnectionExt', // 这里使用自定义数据库连接拓展类 'dsn' => 'mysql:host=127.0.0.1;dbname=test', 'username' => 'root', 'password' => '123456', 'charset' => 'utf8mb4', 'enableSchemaCache' => true, 'schemaCacheDuration' => 60, 'enableLogging' => true, 'commandClass' => 'commonextCommandExt', 'attributes' => [ PDO::ATTR_STRINGIFY_FETCHES => false, PDO::ATTR_EMULATE_PREPARES => false, ] ];
2、Yii2.0 （use version：2.0.31）嵌套事务改造：
重要说明：因为增加了上面两个PDO参数后，Yii2.0的原有嵌套事务执行会报错，主要原因是框架本身的嵌套事务依赖于模拟预处理。所以还需要下面的改造处理。
继承覆写原有的数据库连接类yiidbConnection：
<?php namespace commonext; use yiidbConnection; /** * 数据库连接拓展类 * * Class ConnectionExt * @package commonext */ class ConnectionExt extends Connection { /** * @var Transaction the currently active transaction */ private $_transaction; /** * Returns the currently active transaction. * @return Transaction|null the currently active transaction. Null if no active transaction. */ public function getTransaction() { return $this->_transaction && $this->_transaction->getIsActive() ? $this->_transaction : null; } /** * Starts a transaction. * @param string|null $isolationLevel The isolation level to use for this transaction. * See [[Transaction::begin()]] for details. * @throws * @return Transaction the transaction initiated */ public function beginTransaction($isolationLevel = null) { $this->open(); if (($transaction = $this->getTransaction()) === null) { $transaction = $this->_transaction = new TransactionExt(['db' => $this]); // 这里实例新的事务类 } $transaction->begin($isolationLevel); return $transaction; } }
继承覆写事务处理类yiidbTransaction：
<?php namespace commonext; use Yii; use yiiaseInvalidConfigException; use yiiaseNotSupportedException; use yiidbConnection; use yiidbException; use yiidbTransaction; /** * 事务拓展类 * * Class TransactionExt * @package commonext */ class TransactionExt extends Transaction { /** * @var int the nesting level of the transaction. 0 means the outermost level. */ private $_level = 0; /** * Returns a value indicating whether this transaction is active. * @return bool whether this transaction is active. Only an active transaction * can [[commit()]] or [[rollBack()]]. */ public function getIsActive() { return $this->_level > 0 && $this->db && $this->db->isActive; } /** * 覆写开始事务method * * @param string|null $isolationLevel The [isolation level][] to use for this transaction. * This can be one of [[READ_UNCOMMITTED]], [[READ_COMMITTED]], [[REPEATABLE_READ]] and [[SERIALIZABLE]] but * also a string containing DBMS specific syntax to be used after `SET TRANSACTION ISOLATION LEVEL`. * If not specified (`null`) the isolation level will not be set explicitly and the DBMS default will be used. * * > Note: This setting does not work for PostgreSQL, where setting the isolation level before the transaction * has no effect. You have to call [[setIsolationLevel()]] in this case after the transaction has started. * * > Note: Some DBMS allow setting of the isolation level only for the whole connection so subsequent transactions * may get the same isolation level even if you did not specify any. When using this feature * you may need to set the isolation level for all transactions explicitly to avoid conflicting settings. * At the time of this writing affected DBMS are MSSQL and SQLite. * * [isolation level]: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Isolation_levels * * Starting from version 2.0.16, this method throws exception when beginning nested transaction and underlying DBMS * does not support savepoints. * @throws InvalidConfigException if [[db]] is `null` * @throws NotSupportedException if the DBMS does not support nested transactions * @throws Exception if DB connection fails */ public function begin($isolationLevel = null) { if ($this->db === null) { throw new InvalidConfigException('Transaction::db must be set.'); } $this->db->open(); if ($this->_level === 0) { if ($isolationLevel !== null) { $this->db->getSchema()->setTransactionIsolationLevel($isolationLevel); } Yii::debug('Begin transaction' . ($isolationLevel ? ' with isolation level ' . $isolationLevel : ''), __METHOD__); $this->db->trigger(Connection::EVENT_BEGIN_TRANSACTION); $this->db->pdo->beginTransaction(); $this->_level = 1; return; } $schema = $this->db->getSchema(); if ($schema->supportsSavepoint()) { Yii::debug('Set savepoint ' . $this->_level, __METHOD__); // Notice: solute error: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); $schema->createSavepoint('LEVEL' . $this->_level); $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); } else { Yii::info('Transaction not started: nested transaction not supported', __METHOD__); throw new NotSupportedException('Transaction not started: nested transaction not supported.'); } $this->_level++; } /** * 覆写提交事务method * * Commits a transaction. * @throws Exception */ public function commit() { if (!$this->getIsActive()) { throw new Exception('Failed to commit transaction: transaction was inactive.'); } $this->_level--; if ($this->_level === 0) { Yii::debug('Commit transaction', __METHOD__); $this->db->pdo->commit(); $this->db->trigger(Connection::EVENT_COMMIT_TRANSACTION); return; } $schema = $this->db->getSchema(); if ($schema->supportsSavepoint()) { Yii::debug('Release savepoint ' . $this->_level, __METHOD__); $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); $schema->releaseSavepoint('LEVEL' . $this->_level); $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); } else { Yii::info('Transaction not committed: nested transaction not supported', __METHOD__); } } /** * 覆写回滚事务method * * Rolls back a transaction. */ public function rollBack() { if (!$this->getIsActive()) { // do nothing if transaction is not active: this could be the transaction is committed // but the event handler to "commitTransaction" throw an exception return; } $this->_level--; if ($this->_level === 0) { Yii::debug('Roll back transaction', __METHOD__); $this->db->pdo->rollBack(); $this->db->trigger(Connection::EVENT_ROLLBACK_TRANSACTION); return; } $schema = $this->db->getSchema(); if ($schema->supportsSavepoint()) { Yii::debug('Roll back to savepoint ' . $this->_level, __METHOD__); $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); $schema->rollBackSavepoint('LEVEL' . $this->_level); $this->db->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); } else { Yii::info('Transaction not rolled back: nested transaction not supported', __METHOD__); } } }
【补充说明】
1、PDO相关参数说明：
array( // 强制 PDO 获取的表字段字符的大小写转换，或原样使用列信息 PDO::ATTR_CASE => PDO::CASE_LOWER, // 执行出错时抛出异常 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // 将返回的空字符串转换为 SQL 的 NULL PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL, // 返回数据的时候不将数值转换为字符串 PDO::ATTR_STRINGIFY_FETCHES => false, // 设置为false禁止PDO模拟预处理语句，而使用真正的预处理语句，即有MySQL执行预处理语句 PDO::ATTR_EMULATE_PREPARES => false, ));
2、此api改造不受框架对数据类型的处理，参考文章：https://www.cnblogs.com/itsharehome/p/12275979.html