如何在Eloquent中记录来自多个连接的每个查询(在laravel之外)

Question

我在我的应用程序中使用多个数据库连接，一个SQLServ和另一个MySQL。我想按顺序调试来自两个服务器的每个查询。因此，与其使用Manager::getQueryLog()，我更需要使用Event::listen。我使用SlimFramework，使用PHP-DI。

index . php

// Create container & database
$containerBuilder = new DIContainerBuilder(AppLibContainer::class);
$containerBuilder->useAnnotations(false);
$containerBuilder->addDefinitions(__DIR__ . '/../config/settings.php');
$container = $containerBuilder->build();
$app = DIBridgeSlimBridge::create($container);
// Register database
$capsule = new IlluminateDatabaseCapsuleManager();
foreach ($container->get('database') as $con => $config) {
$capsule->addConnection($config, $con);
}
$capsule->setEventDispatcher(new IlluminateEventsDispatcher()); 
// Throw error A facade root has not been set. pretty sure it 
// was because i use it outside laravel
$capsule->setAsGlobal();
$capsule->bootEloquent();
$container->set('connection', $capsule);
// Listen
DB::listen(function($query) {
Log::info(
$query->sql,
$query->bindings,
$query->time
);
});

App Lib 容器::类

namespace AppLib;
use DIContainer as DIContainer;
class Container extends DIContainer
{
public function __get($key)
{
if ($this->has($key)) {
return $this->get($key);
}
}
}

如何记录来自多个连接的每个查询，但是按照顺序，如下所示:

select * from tableInMySQL limit 0,10;
select TOP 10 * from [tableInMSSQL];
update tableInMySQL set field='value';

编辑

如前所述，我使用SlimFramework, PHP-DI.
所以，我不使用LARAVEL作为一个整体。(不使用service provider)

DB::listen抛出错误，$capsule->getConnection('con_name')->getEventDispatcher()返回null

Answer 1

按顺序记录多个数据库连接的查询，我们首先需要enableQueryLog，配置单独的Logger。

$capsule->setAsGlobal();
$capsule->bootEloquent();
$container->set('connection', $capsule);
$capsule->connection('<MySqlConnectionName>')->enableQueryLog();
$capsule->connection('<SqlServerConnectionName>')->enableQueryLog();
//$capsule->connection('<MongoConnectionName>')->enableQueryLog(); 
// Listen
IlluminateDatabaseCapsuleManager::listen(function($query) {
if($query->connectionName == 'mysql') {
$mysqlLogger->debug('mysql', [
'query' => $query->sql,
'bindings' => $query->bindings
]);
} elseif($query->connectionName == 'sqlserver') {
$sqlServerLogger->debug('mongodb', [
'query' => $query->sql,
'bindings' => $query->bindings
]);
} /*elseif($query->connectionName == 'mongodb') {
$mongoDbLogger->debug('mongodb', [
'query' => $query->sql,
'bindings' => $query->bindings
]);
}*/
});

Answer 2

对于任何在这里登陆的人，我们需要在每个连接上附加侦听器

setting.php

return [
'maintenance' => false,
'base_path'   => empty($_ENV['SUBDIR']) ? '' : '/' . $_ENV['SUBDIR'],
'database'    => [
'default'  => [ // sql server
"driver"   => env('DB_MSSQL_DRIVER'),
"host"     => env('DB_MSSQL_HOST'),
"port"     => env('DB_MSSQL_PORT'),
"database" => env('DB_MSSQL_DATABASE'),
"username" => env('DB_MSSQL_USERNAME'),
'password' => env('DB_MSSQL_PASSWORD'),
],
'mysql'  => [
"driver"   => env('DB_MYSQL_DRIVER'),
"host"     => env('DB_MYSQL_HOST'),
"port"     => env('DB_MYSQL_PORT'),
"database" => env('DB_MYSQL_DATABASE'),
"username" => env('DB_MYSQL_USERNAME'),
'password' => env('DB_MYSQL_PASSWORD'),
],
],
"template"    => [
"view"  => __DIR__ . "/../module",
"cache" => __DIR__ . "/../cache",
],
];

index . php

// build PHP-DI container 
$container = $containerBuilder->build();
//register database
$capsule = new IlluminateDatabaseCapsuleManager();
foreach ($container->get('database') as $con => $config) {
$capsule->addConnection($config, $con);
$capsule->getConnection($con)->setEventDispatcher(new IlluminateEventsDispatcher());
$capsule->getConnection($con)->listen(function ($q) {
// or use logger
$d = str_replace('?', "'?'", "[$q->connectionName]t" . $q->sql);
$d = vsprintf(str_replace('?', '%s', $d), $q->bindings);
file_put_contents(__DIR__ . '/../log/sql.log', $d . PHP_EOL, FILE_APPEND);
});
}
$capsule->setAsGlobal();
$capsule->bootEloquent();
$container->set('connection', $capsule);

Answer 3

应该在app/Providers/AppServiceProvider.php的boot()函数中写入DB::listen事件

DB::listen(function ($query) {
$qry = str_replace(['?'], [''%s''], $query->sql);
$qry = vsprintf($qry, $query->bindings);
Log::info('query_log', [
'query' => $qry,
'bindings' => $query->bindings,
]);
});

Answer 4

请将此添加到Providers/AppServiceProvider.php文件中，并在laravel日志文件

中检查它们

use IlluminateSupportFacadesEvent;
use IlluminateSupportFacadesLog;
public function register() {
Event::listen('IlluminateDatabaseEventsQueryExecuted', function ($query) {
Log::debug($query->sql . ' - ' . serialize($query->bindings));
});
}

Answer 5

我将这些代码放入AppServiceProvider.php的boot()方法

if (App::environment('local')) {
DB::listen(function ($query) {
Log::info($query->sql);
});
}

因此，日志将只用于开发环境。