README

A tiny extension of MySqlConnection that manages session system variables

Requirements

Note

Older versions have outdated dependency requirements. If you cannot prepare the latest environment, please refer to past releases.

Installing

composer require mpyw/laravel-mysql-system-variable-manager

Basic Usage

Important

The default implementation is provided by MySqlConnectionServiceProvider, however, package discovery is not available. Be careful that you MUST register it in config/app.php by yourself.

<?php

return [

    /* ... */

    'providers' => [
        /* ... */

        Mpyw\LaravelMySqlSystemVariableManager\MySqlConnectionServiceProvider::class,

        /* ... */
    ],

];

<?php

use Illuminate\Support\Facades\DB;

// Assign an auto-recoverable system variable
// The variable is reassigned on accidental disconnections
DB::setSystemVariable('long_query_time', 10.0);

// Assign a system variable without auto-recovery
DB::setSystemVariable('long_query_time', 10.0, false);

// Assign multiple variables
DB::setSystemVariables(['long_query_time' => 10.0, 'transaction_isolation' => 'read-committed']);

// Assign a variable on a different connection
DB::connection('other_mysql_connection')->setSystemVariable('long_query_time', 10.0);

// Run callback temporarily assigning a variable
DB::usingSystemVariable('long_query_time', 10.0, function () {
    /* ... */
});

// Run callback temporarily assigning multiple variables
DB::usingSystemVariables(['long_query_time' => 10.0, 'transaction_isolation' => 'read-committed'], function () {
    /* ... */
});

// Run callback replacing current value
// NOTE: You MUST declare closure return types.
DB::usingSystemVariables(
    [
        'long_query_time' => function (float $currentValue): float {
             return $currentValue + 5.0;
        },
        'sql_mode' => function (string $currentValue): string {
             return str_replace('ONLY_FULL_GROUP_BY', '', $currentValue);
        },
    ],
    function () {
        /* ... */
    }
);

Caution

Don't use DB::disconnect() directly or auto-recovery won't be fired.
Use DB::connection()->disconnect() instead.

Advanced Usage

Tip

You can extend MySqlConnection with ManagesSystemVariables trait by yourself.

<?php

namespace App\Providers;

use App\Database\MySqlConnection;
use Illuminate\Database\Connection;
use Illuminate\Support\ServiceProvider;

class DatabaseServiceProvider extends ServiceProvider
{
    public function register(): void
    {
        Connection::resolverFor('mysql', function (...$parameters) {
            return new MySqlConnection(...$parameters);
        });
    }
}

<?php

namespace App\Database;

use Illuminate\Database\Connection as BaseMySqlConnection;
use Mpyw\LaravelMySqlSystemVariableManager\ManagesSystemVariables;

class MySqlConnection extends BaseMySqlConnection
{
    use ManagesSystemVariables;
    
    public function withoutForeignKeyChecks(callable $callback, ...$args)
    {
        return $this->usingSystemVariable('foreign_key_checks', false, $callback, ...$args);
    }
    
    public function allowingPartialGroupBy(callable $callback, ...$args)
    {
        return $this->usingSystemVariable('sql_mode', function (string $mode): string {
            return str_replace('ONLY_FULL_GROUP_BY', '', $mode);
        }, $callback, ...$args);
    }
}

<?php

use App\Post;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

$post = new Post();
$post->user()->associate(Auth::user());
$post->save();

DB::withoutForeignKeyChecks(function () use ($post) {
    $post->user()->associate(null);
    $post->save();
});