b2pweb/bdf-prime-analyzer

Analyze queries generated by Prime ORM and dump unsafe or not optimized queries

v1.0.3 2024-02-27 08:31 UTC

Requires

• php: ~7.1 || ~8.0.0 || ~8.1.0 || ~8.2.0 || ~8.3.0
• ext-json: *
• b2pweb/bdf-collections: ~1.1.2
• b2pweb/bdf-prime: ~1.0|^2.0.5

Requires (Dev)

• phpunit/phpunit: ~7.0|~8.0
• vimeo/psalm: ~4.7

Suggests

• b2p/bdf-web: For use service provider (~1.8)

Conflicts

• phpdocumentor/reflection-docblock: >=5.3

MIT

• Vincent Quatrevieux

• orm
• analyzer
• prime

README

Analyse executed queries during runtime to report suspicious queries or optimisations tips.

Usage

Install using composer

composer require --dev b2p/bdf-prime-analyzer

Register on unit test case

    public static function createApplication()
    {
        $app = require __DIR__ . '/../../app/application.php';
        // ...
        $app->register(new \Bdf\Prime\Analyzer\PrimeAnalyzerServiceProvider([
            'ignoredPath' => [dirname(__DIR__)],
            'dumpFormats' => [
                new \Bdf\Prime\Analyzer\Testing\DumpFormat\HtmlDumpFormat(__DIR__.'/prime-report.html'),
                new \Bdf\Prime\Analyzer\Testing\DumpFormat\ConsoleDumpFormat(),
            ],
            'ignoredAnalysis' => [],
        ]));
        
        // ...

        return $app;
    }

To ignore analysis on a single query, simple add the @prime-analyzer-ignore [analisys] tag on the execute line.

$credentials = $device->relation('credentials')->first(); // @prime-analyzer-ignore optimisation sort

You can also ignore analysis globally on the entity, using the docblock of the Mapper class. Unlike the query inline ignore, parameters can be set to ignore only the listed attributes.

/**
 * Mapper for TourOperation
 *
 * @prime-analyzer-ignore sort position
 * @prime-analyzer-ignore not_declared my_raw_attribute
 * @prime-analyzer-ignore index
 */
class TourOperationMapper extends Mapper
{
    // ...
}

Options

Available options to set on the service provider registration :

• ignoredPath : List of path to ignore on the query analysis. For example the tests directory.
• ignoredAnalysis : List of analysis to ignore. See AnalysisTypes. AnalysisTypes::optimisations() can be used to only report dangerous queries.
• dumpFormats : List of DumpFormatInterface instances.
  • ConsoleDumpFormat : for dump in console after the end of tests
  • HtmlDumpFormat : for dump into an HTML file
• register : Set false to disable reporting

Analysis

• like : Analyse the LIKE filters, and dump values without wildcard _ or %.
• index : Dump queries without any indexes. Queries with at least one relation or join are ignored.
• not_declared : Dump use of undeclared attributes (i.e. attributes not in mapper).
• sort : Dump queries with an ORDER clause on non-indexed attribute. You may ignore those reports if the returned set is small.
• or : Dump queries with an OR clause not nested into parenthesis.
• optimisation : Dump queries which can be optimised using KeyValueQuery or findById. See limitations for more information.
• relation_distant_key : Dump queries using the distant key of a relation instead of the local key. This cause a useless join. Do not handle "through" relations.
• write : Dump invalid update or insert queries. Analyse undeclared attributes and values types.
• n+1 : Dump N+1 or loop queries. To raise an N+1, the queries must be at least called twice with the same stacktrace. Ignore only if the loop is desired (like walker).

Limitations

• Do not handle raw expressions or join.
• optimisation may generate report on relation query, which cannot be fixed, and should be ignored.
• n+1 may report a false positive in case of load() two relations on same entity (ex: $orderCustomer.load(['lading', 'delivery'])).
• Always verify indexes. The index analysis do not replace an EXPLAIN.