spiral/framework

Enhancement Proposals for Spiral Database Seeder Package

butschster opened this issue · 2 comments

butschster commented

Introduction

The spiral-packages/database-seeder in the Spiral framework provides a useful testing SDK for the Cycle Database and ORM. However, there are some areas which could be improved to provide a more streamlined and feature-rich experience for developers. Here are several proposed enhancements:

Proposals

1. Deprecation of Spiral\DatabaseSeeder\TestCase:

Currently, in a Spiral application, we extend Spiral\Testing\TestCase for application test case Tests\TestCase, but seeders tests case also extends Spiral\Testing\TestCase which eliminates the possibility of using the application Tests\TestCase. It appears that Spiral\DatabaseSeeder\TestCase is not needed and thus proposing its deprecation.

2. Refactor Setup and Teardown for Migration and Cleanup:

The setup process for migrating the database schema and cleaning up the database on teardown could be refactored as follows:

protected function setUp(): void
{
    parent::setUp();
    $this->dbDriver = $this->get(DatabaseInterface::class)->getDriver();
    if (!DatabaseState::$migrated) {
        $this->runCommand('migrate', ['--force' => true]);
        DatabaseState::$migrated = true;
    }
    $this->dbDriver->beginTransaction();
}

protected function tearDown(): void
{
    parent::tearDown();
    $this->dbDriver->rollbackTransaction();
}

Additionally, it would be beneficial to suggest developers on different migration strategies like running application migrations, importing database schema with data from SQL file etc., with the ability to create custom strategies.

3. Foreign Key constraints check Toggle in cycle/database:

Currently, there is no provision to enable/disable foreign keys checks in the Cycle database. Adding this functionality would be helpful.

4. Introduction of DatabaseCleaner Class:

A DatabaseCleaner class in the seeder will provide the functionality to clean (truncate) database tables when needed, which is currently missing in the testing package.

Something like this:

use Cycle\Database\DatabaseProviderInterface;
use Cycle\Database\Driver\MySQL\MySQLDriver;
use Cycle\Database\Driver\Postgres\PostgresDriver;
use Cycle\Database\Driver\SQLite\SQLiteDriver;

final readonly class DatabaseCleaner
{
    public function __construct(
        private DatabaseProviderInterface $provider,
    ) {
    }

    public function truncateTable(string $table, ?string $database = null, bool $foreignKeyConstraints = true): void
    {
        $db = $this->provider->database($database);

        if (!$foreignKeyConstraints) {
            $this->disableForeignKeyConstraintsCheck($database);
        }

        $table = $db->getDriver()->getQueryCompiler()->quoteIdentifier($table);

        if ($db->getDriver() instanceof PostgresDriver) {
            $query = \sprintf('TRUNCATE TABLE %s RESTART IDENTITY CASCADE', $table);
        } elseif ($db->getDriver() instanceof SQLiteDriver) {
            $query = \sprintf('DELETE FROM %s', $table);
        } else {
            $query = \sprintf('TRUNCATE TABLE %s', $table);
        }

        $db->execute($query);

        if (!$foreignKeyConstraints) {
            $this->enableForeignKeyConstraintsCheck($database);
        }
    }

    public function refreshDb(?string $database = null, array $except = []): void
    {
        $db = $this->provider->database($database);

        foreach ($db->getTables() as $table) {
            $name = \explode('.', $table->getFullName(), 2);

            if (\count($name) > 1) {
                $tableName = $name[1];
            } else {
                $tableName = $name[0];
            }

            if (\in_array($tableName, $except, true)) {
                continue;
            }

            $this->truncateTable($table->getFullName(), database: $database, foreignKeyConstraints: false);
        }
    }
}

5. Incorporation of StdOutLogger for SQL Query Logging:

Sometimes, it's crucial to investigate the SQL commands executed during tests.

use Psr\Log\LoggerInterface;
use Psr\Log\LoggerTrait;

final class StdoutLogger implements LoggerInterface
{
    use LoggerTrait;

    /** @var list<string> */
    private array $informationalMarkers = [
        'pgc.oid',
        'information_schema',
        'pg_catalog',
        'pg_type',
        'pg_indexes',
        'pg_constraint',
    ];

    public function log($level, \Stringable|string $message, array $context = []): void
    {
        $message = (string)$message;
        // skip informational queries
        foreach ($this->informationalMarkers as $marker) {
            if (\str_contains($message, $marker)) {
                return;
            }
        }

        echo $this->colorMessage($message) . PHP_EOL;
    }

    private function colorMessage(string $sql): string
    {
        // Colors SQL using ANSI colors
        $sql = preg_replace(
            '/\b(SELECT|UPDATE|INSERT|DELETE)\b/i',
            "\033[1;32m$1\033[0m",
            $sql,
        );

        $keyword = '/\b(ADD|ALTER|AND|AS|ASC|BETWEEN|BY|CASE|CHECK|COLUMN|CONSTRAINT|CREATE|CROSS|CURRENT_DATE|CURRENT_TIME|CURRENT_TIMESTAMP|DATABASE|DEFAULT|DELETE|DESC|DISTINCT|DROP|ELSE|END|EXISTS|FOREIGN|FROM|FULL|GROUP|HAVING|IN|INDEX|INNER|INSERT|INTERSECT|INTO|IS|JOIN|LEFT|LIKE|LIMIT|NOT|NULL|ON|OR|ORDER|OUTER|PRIMARY|REFERENCES|RIGHT|SELECT|SET|TABLE|THEN|TO|TRUNCATE|UNION|UNIQUE|UPDATE|VALUES|WHEN|WHERE)\b/i';

        return \preg_replace(
            $keyword,
            "\033[1;31m$1\033[0m",
            $sql,
        );
    }
}

A StdOutLogger could be introduced for dumping SQL queries, which can be enabled as needed in test cases, for example:

public function testGetsByPk(): void
{
    $this->showDatabaseQueries();  // <====will enable SQL logs
    $server = ServerFactory::new()->createOne();
    $this->servers->getByPk($server->uuid);
}

Or using attributes:

#[ShowQueries]  // <====will enable SQL logs
public function testGetsByPk(): void
{
    $server = ServerFactory::new()->createOne();
    $this->servers->getByPk($server->uuid);
}

6. TableAssertion Class for Table Assertions:

Introduce a TableAssertion class to handle assertions related to database tables like:

$this->assertTable('users')->where(['role' => 'admin'])->assertCount(5);
$this->assertTable('users')->where(['role' => 'guest'])->assertEmpty();

7. EntityAssertion Class for ORM Entity Assertions:

Similarly, an EntityAssertion class can handle assertions related to ORM entities:

$this->assertEntity(User::class)->withoutScope()->where(['role' => 'admin'])->assertExists();
$this->assertEntity(User::class)->withoutScope()->where(['role' => 'guest'])->assertMissing();

These proposed improvements aim to offer a better and more streamlined developer experience. Feedback and further suggestions are welcome.

butschster commented
  1. Deprecate TestCase in seeder
  2. Strategy for running database migration in tests
  3. Add ForeignKeyConstraintsCheck on/off in cylce/database
  4. Look in runCommand('migrate', ['--force' => true]) how many transactions are launched and how many are committed
  5. Add DatabaseCleaner in the seeder
  6. Investigate why AbstractTable in HandlerInterface
  7. Add StdOutLogger to the seeder for dumping SQL queries
  8. Add TableAssertion
  9. Add EntityAssertion