illuminate/database 使用 五

之前文章：

illuminate/database 使用 一-CSDN博客

illuminate/database 使用 二-CSDN博客

illuminate/database 使用 三-CSDN博客

illuminate/database 使用 四-CSDN博客

一、原生查询

1.1 原理

        根据之前内容调用执行的静态类为Illuminate\Database\Capsule\Manager类。

        根据其源码调用其未定义的静态类，返回的是Illuminate\Database\MySqlConnection类对应的静态类。

        调用顺序Manager->DatabaseManager->ConnectionFactory->MySqlConnection。

        Manager通过对象的定义和调用，实现调用DatabaseManager类。

        ConnectionFactory通过工厂模式调用MySqlConnection。

        MySqlConnection继承Connection。

        Connection中可用方法：

• selectOne($query, $bindings = [], $useReadPdo = true) 查询并返回一行

• selectFromWriteConnection($query, $bindings = []) 从读库中查询并返回

• select($query, $bindings = [], $useReadPdo = true) 查询

• cursor($query, $bindings = [], $useReadPdo = true)

• insert($query, $bindings = []) 插入数据 返回布尔执行结果

• update($query, $bindings = []) 修改数据 返回影响行数

• delete($query, $bindings = []) 删除数据

• statement($query, $bindings = []) 返回布尔执行结果

• affectingStatement($query, $bindings = []) 返回结果影响行数

• unprepared($query) 对PDO连接运行一个未准备的原始查询

1.2 实现代码

function test2()
{$sql = "select * from userinfo where id=:id";$info = Capsule::select($sql, ['id' => 1]);var_dump($info);$sql = "select * from userinfo where id in (?) order by id desc";$info = Capsule::selectOne($sql, [2, 3]);var_dump($info);$sql = "insert into userinfo(name,age) values(:name,:age)";$row = Capsule::insert($sql, ['name' => 'name', 'age' => 10]);var_dump($row);$sql = "update userinfo set name=:name where id=:id";$row = Capsule::update($sql, ['name' => 'name2', 'id' => 4]);var_dump($row);$sql = "select * from userinfo where id=:id";$info = Capsule::statement($sql, ['id' => 1]);var_dump($info);$sql = "select * from userinfo where id=:id";$info = Capsule::affectingStatement($sql, ['id' => 1]);var_dump($info);$sql = "insert into userinfo(name,age) values('name1','10')";$info = Capsule::unprepared($sql);var_dump($info);$sql = "delete from userinfo where name='name1'";$row = Capsule::delete($sql);var_dump($row);
}
test2();

 执行结果：

array(1) {[0] =>class stdClass#15 (3) {public $id =>int(1)public $name =>string(3) "123"public $age =>int(22)}
}
class stdClass#17 (3) {public $id =>int(2)public $name =>string(5) "name2"public $age =>int(13)
}
bool(true)
int(0)
bool(true)
int(1)
bool(true)
int(1)

1.3 源代码

namespace Illuminate\Database\Capsule;
use Illuminate\Container\Container;
use Illuminate\Contracts\Events\Dispatcher;
use Illuminate\Database\Connectors\ConnectionFactory;
use Illuminate\Database\DatabaseManager;
use Illuminate\Database\Eloquent\Model as Eloquent;
use Illuminate\Support\Traits\CapsuleManagerTrait;
use PDO;class Manager
{/*** Dynamically pass methods to the default connection.** @param  string  $method* @param  array  $parameters* @return mixed*/public static function __callStatic($method, $parameters){return static::connection()->$method(...$parameters);}/*** Get a connection instance from the global manager.** @param  string|null  $connection* @return \Illuminate\Database\Connection*/public static function connection($connection = null){return static::$instance->getConnection($connection);}/*** Get a registered connection instance.** @param  string|null  $name* @return \Illuminate\Database\Connection*/public function getConnection($name = null){return $this->manager->connection($name);}/*** Create a new database capsule manager.** @param  \Illuminate\Container\Container|null  $container* @return void*/public function __construct(Container $container = null){$this->setupContainer($container ?: new Container);// Once we have the container setup, we will setup the default configuration// options in the container "config" binding. This will make the database// manager work correctly out of the box without extreme configuration.$this->setupDefaultConfiguration();$this->setupManager();}/*** Build the database manager instance.** @return void*/protected function setupManager(){$factory = new ConnectionFactory($this->container);$this->manager = new DatabaseManager($this->container, $factory);}
}

namespace Illuminate\Database;use Doctrine\DBAL\Types\Type;
use Illuminate\Database\Connectors\ConnectionFactory;
use Illuminate\Support\Arr;
use Illuminate\Support\ConfigurationUrlParser;
use Illuminate\Support\Str;
use InvalidArgumentException;
use PDO;
use RuntimeException;class DatabaseManager implements ConnectionResolverInterface
{/*** Create a new database manager instance.** @param  \Illuminate\Contracts\Foundation\Application  $app* @param  \Illuminate\Database\Connectors\ConnectionFactory  $factory* @return void*/public function __construct($app, ConnectionFactory $factory){$this->app = $app;$this->factory = $factory;$this->reconnector = function ($connection) {$this->reconnect($connection->getNameWithReadWriteType());};}/*** Reconnect to the given database.** @param  string|null  $name* @return \Illuminate\Database\Connection*/public function reconnect($name = null){$this->disconnect($name = $name ?: $this->getDefaultConnection());if (!isset($this->connections[$name])) {return $this->connection($name);}return $this->refreshPdoConnections($name);}/*** Get a database connection instance.** @param  string|null  $name* @return \Illuminate\Database\Connection*/public function connection($name = null){[$database, $type] = $this->parseConnectionName($name);$name = $name ?: $database;// If we haven't created this connection, we'll create it based on the config// provided in the application. Once we've created the connections we will// set the "fetch mode" for PDO which determines the query return types.if (!isset($this->connections[$name])) {$this->connections[$name] = $this->configure($this->makeConnection($database), $type);}return $this->connections[$name];}/*** Make the database connection instance.** @param  string  $name* @return \Illuminate\Database\Connection*/protected function makeConnection($name){$config = $this->configuration($name);// First we will check by the connection name to see if an extension has been// registered specifically for that connection. If it has we will call the// Closure and pass it the config allowing it to resolve the connection.if (isset($this->extensions[$name])) {return call_user_func($this->extensions[$name], $config, $name);}// Next we will check to see if an extension has been registered for a driver// and will call the Closure if so, which allows us to have a more generic// resolver for the drivers themselves which applies to all connections.if (isset($this->extensions[$driver = $config['driver']])) {return call_user_func($this->extensions[$driver], $config, $name);}return $this->factory->make($config, $name);}
}

namespace Illuminate\Database\Connectors;use Illuminate\Contracts\Container\Container;
use Illuminate\Database\Connection;
use Illuminate\Database\MySqlConnection;
use Illuminate\Database\PostgresConnection;
use Illuminate\Database\SQLiteConnection;
use Illuminate\Database\SqlServerConnection;
use Illuminate\Support\Arr;
use InvalidArgumentException;
use PDOException;class ConnectionFactory
{/*** Establish a PDO connection based on the configuration.** @param  array  $config* @param  string|null  $name* @return \Illuminate\Database\Connection*/public function make(array $config, $name = null){$config = $this->parseConfig($config, $name);if (isset($config['read'])) {return $this->createReadWriteConnection($config);}return $this->createSingleConnection($config);}/*** Create a single database connection instance.** @param  array  $config* @return \Illuminate\Database\Connection*/protected function createSingleConnection(array $config){$pdo = $this->createPdoResolver($config);return $this->createConnection($config['driver'], $pdo, $config['database'], $config['prefix'], $config);}/*** Create a new connection instance.** @param  string  $driver* @param  \PDO|\Closure  $connection* @param  string  $database* @param  string  $prefix* @param  array  $config* @return \Illuminate\Database\Connection** @throws \InvalidArgumentException*/protected function createConnection($driver, $connection, $database, $prefix = '', array $config = []){if ($resolver = Connection::getResolver($driver)) {return $resolver($connection, $database, $prefix, $config);}switch ($driver) {case 'mysql':return new MySqlConnection($connection, $database, $prefix, $config);case 'pgsql':return new PostgresConnection($connection, $database, $prefix, $config);case 'sqlite':return new SQLiteConnection($connection, $database, $prefix, $config);case 'sqlsrv':return new SqlServerConnection($connection, $database, $prefix, $config);}throw new InvalidArgumentException("Unsupported driver [{$driver}].");}
}

namespace Illuminate\Database;use Doctrine\DBAL\Driver\PDOMySql\Driver as DoctrineDriver;
use Doctrine\DBAL\Version;
use Illuminate\Database\PDO\MySqlDriver;
use Illuminate\Database\Query\Grammars\MySqlGrammar as QueryGrammar;
use Illuminate\Database\Query\Processors\MySqlProcessor;
use Illuminate\Database\Schema\Grammars\MySqlGrammar as SchemaGrammar;
use Illuminate\Database\Schema\MySqlBuilder;
use Illuminate\Database\Schema\MySqlSchemaState;
use Illuminate\Filesystem\Filesystem;
use PDO;class MySqlConnection extends Connection
{}

 

namespace Illuminate\Database;use Closure;
use DateTimeInterface;
use Doctrine\DBAL\Connection as DoctrineConnection;
use Doctrine\DBAL\Types\Type;
use Exception;
use Illuminate\Contracts\Events\Dispatcher;
use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Database\Events\StatementPrepared;
use Illuminate\Database\Events\TransactionBeginning;
use Illuminate\Database\Events\TransactionCommitted;
use Illuminate\Database\Events\TransactionRolledBack;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Query\Expression;
use Illuminate\Database\Query\Grammars\Grammar as QueryGrammar;
use Illuminate\Database\Query\Processors\Processor;
use Illuminate\Database\Schema\Builder as SchemaBuilder;
use Illuminate\Support\Arr;
use LogicException;
use PDO;
use PDOStatement;
use RuntimeException;class Connection implements ConnectionInterface
{/*** Get a new query builder instance.** @return \Illuminate\Database\Query\Builder*/public function query(){return new QueryBuilder($this, $this->getQueryGrammar(), $this->getPostProcessor());}/*** Run a select statement against the database.** @param  string  $query* @param  array  $bindings* @param  bool  $useReadPdo* @return array*/public function select($query, $bindings = [], $useReadPdo = true){return $this->run($query, $bindings, function ($query, $bindings) use ($useReadPdo) {if ($this->pretending()) {return [];}// For select statements, we'll simply execute the query and return an array// of the database result set. Each element in the array will be a single// row from the database table, and will either be an array or objects.$statement = $this->prepared($this->getPdoForSelect($useReadPdo)->prepare($query));$this->bindValues($statement, $this->prepareBindings($bindings));$statement->execute();return $statement->fetchAll();});}/*** Run an insert statement against the database.** @param  string  $query* @param  array  $bindings* @return bool*/public function insert($query, $bindings = []){return $this->statement($query, $bindings);}/*** Run an update statement against the database.** @param  string  $query* @param  array  $bindings* @return int*/public function update($query, $bindings = []){return $this->affectingStatement($query, $bindings);}/*** Run a delete statement against the database.** @param  string  $query* @param  array  $bindings* @return int*/public function delete($query, $bindings = []){return $this->affectingStatement($query, $bindings);}/*** Run a raw, unprepared query against the PDO connection.** @param  string  $query* @return bool*/public function unprepared($query){return $this->run($query, [], function ($query) {if ($this->pretending()) {return true;}$this->recordsHaveBeenModified($change = $this->getPdo()->exec($query) !== false);return $change;});}/*** Execute the given callback in "dry run" mode.** @param  \Closure  $callback* @return array*/public function pretend(Closure $callback){return $this->withFreshQueryLog(function () use ($callback) {$this->pretending = true;// Basically to make the database connection "pretend", we will just return// the default values for all the query methods, then we will return an// array of queries that were "executed" within the Closure callback.$callback($this);$this->pretending = false;return $this->queryLog;});}/*** Execute an SQL statement and return the boolean result.** @param  string  $query* @param  array  $bindings* @return bool*/public function statement($query, $bindings = []){return $this->run($query, $bindings, function ($query, $bindings) {if ($this->pretending()) {return true;}$statement = $this->getPdo()->prepare($query);$this->bindValues($statement, $this->prepareBindings($bindings));$this->recordsHaveBeenModified();return $statement->execute();});}
}

二、 sql输出

2.1 原理

       Illuminate\Database\MySqlConnection类包含变量queryLog，获取对应执行语句实际上就是获取queryLog。根据源码每次执行sql语句，都使用该类run()函数执行，run()中包括logQuery()函数。而logQuery函数就是对queryLog变量进行设置。

        返回的queryLog变量值，顺序和执行顺序相同，所以取左后一条就是最新执行的sql。

        框架中默认记录不记录queryLog。

2.2 实现代码

function test3()
{Capsule::enableQueryLog();$sql = "select * from userinfo where id=:id";$info = Capsule::select($sql, ['id' => 1]);$sql = "select * from userinfo where id in (?) order by id desc";$info = Capsule::selectOne($sql, [2, 3]);$logs = Capsule::getQueryLog();var_dump($logs);var_dump(end($logs));
}
test3();

 执行结果

array(2) {[0] =>array(3) {'query' =>string(35) "select * from userinfo where id=:id"'bindings' =>array(1) {'id' =>int(1)}'time' =>double(7.35)}[1] =>array(3) {'query' =>string(55) "select * from userinfo where id in (?) order by id desc"'bindings' =>array(2) {[0] =>int(2)[1] =>int(3)}'time' =>double(0.33)}
}
array(3) {'query' =>string(55) "select * from userinfo where id in (?) order by id desc"'bindings' =>array(2) {[0] =>int(2)[1] =>int(3)}'time' =>double(0.33)
}

说实话，输出结果和想的不太一样。我还是希望传回拼接后的语句，当然传$sql的时候就可以传拼接后的sql语句。

因为需要打印sql大概有两种情况

1. 调试sql，对于使用复杂sql时很实用。
2. 调试数据导致的bug。实际运行，bug情况很多，有些是数据导致，查看对应业务的sql执行有时候能快速发现问题。

2.3 源码

class Connection implements ConnectionInterface
{/*** All of the queries run against the connection.** @var array*/protected $queryLog = [];/*** Indicates whether queries are being logged.** @var bool*/protected $loggingQueries = false;/*** Enable the query log on the connection.** @return void*/public function enableQueryLog(){$this->loggingQueries = true;}/*** Disable the query log on the connection.** @return void*/public function disableQueryLog(){$this->loggingQueries = false;}/*** Log a query in the connection's query log.** @param  string  $query* @param  array  $bindings* @param  float|null  $time* @return void*/public function logQuery($query, $bindings, $time = null){$this->event(new QueryExecuted($query, $bindings, $time, $this));if ($this->loggingQueries) {$this->queryLog[] = compact('query', 'bindings', 'time');}}/*** Run a SQL statement and log its execution context.** @param  string  $query* @param  array  $bindings* @param  \Closure  $callback* @return mixed** @throws \Illuminate\Database\QueryException*/protected function run($query, $bindings, Closure $callback){foreach ($this->beforeExecutingCallbacks as $beforeExecutingCallback) {$beforeExecutingCallback($query, $bindings, $this);}$this->reconnectIfMissingConnection();$start = microtime(true);// Here we will run this query. If an exception occurs we'll determine if it was// caused by a connection that has been lost. If that is the cause, we'll try// to re-establish connection and re-run the query with a fresh connection.try {$result = $this->runQueryCallback($query, $bindings, $callback);} catch (QueryException $e) {$result = $this->handleQueryException($e, $query, $bindings, $callback);}// Once we have run the query we will calculate the time that it took to run and// then log the query, bindings, and execution time so we will report them on// the event that the developer needs them. We'll log time in milliseconds.$this->logQuery($query, $bindings, $this->getElapsedTime($start));return $result;}/*** Run a raw, unprepared query against the PDO connection.** @param  string  $query* @return bool*/public function unprepared($query){return $this->run($query, [], function ($query) {if ($this->pretending()) {return true;}$this->recordsHaveBeenModified($change = $this->getPdo()->exec($query) !== false);return $change;});}
}