Для получения полного доступа
зарегистрируйтесь.

Insolita    7   5 927


Видимо, пользователь решил о себе ничего не говорить.
  • Иркутск
  • Зарегистрирован 5 лет назад
Профиль завершён на 20 %
20 %
<?php
namespace common\tests;

use Codeception\Util\Debug;
use yii\console\Application;
use yii\db\ActiveQuery;
use yii\db\Connection;
use yii\helpers\ArrayHelper;
use yii\helpers\Console;
use yii\helpers\VarDumper;

/**
 * Useful set for performance optimization in console scripts, and test researches
 *
 * Trait ProfilingInspectorTrait
 *
 * @package common\tests
 */
trait ProfilingInspectorTrait
{
    
    /**
     * @param \yii\db\Query|ActiveQuery $query
     * @param \yii\db\Connection        $db
     */
    protected function showQuery(\yii\db\Query $query, Connection $db)
    {
        $sql = $query->prepare($db->queryBuilder)->createCommand($db)->rawSql;
        $this->resolvedOutput($sql, __FUNCTION__);
    }
    
    /**
     * @param \yii\db\Query|ActiveQuery $query
     * @param \yii\db\Connection        $db
     */
    protected function explainQuery(\yii\db\Query $query, Connection $db)
    {
        $sql = $query->prepare($db->queryBuilder)->createCommand($db)->rawSql;
        $sql = 'EXPLAIN: ' . implode(PHP_EOL, $db->createCommand('EXPLAIN ' . $sql)->queryColumn('QUERY PLAN'));
        $this->resolvedOutput($sql, __FUNCTION__);
    }
    
    /**
     * @param \yii\db\Query|ActiveQuery $query
     * @param \yii\db\Connection        $db
     */
    protected function analyzeQuery(\yii\db\Query $query, Connection $db)
    {
        $sql = $query->prepare($db->queryBuilder)->createCommand($db)->rawSql;
        $sql = 'ANALYZE: ' . implode(PHP_EOL, $db->createCommand('EXPLAIN ANALYZE ' . $sql)->queryColumn('QUERY PLAN'));
        $this->resolvedOutput($sql, __FUNCTION__);
    }
    
    /**
     * Show execution time for specified callback and time elapsed from app start
     * Return real function result
     *
     * @example
     *         $result=$this->timeIt(function(){
     *         $data = User::find()->where(['<','id', 100])->all();
     *         return ArrayHelper::map($data,'id','name');
     *         });
     *
     * @param \Closure $function
     * @param string   $comment Additional log comment
     *
     * @return mixed
     */
    protected function timeIt(\Closure $function, string $comment = '')
    {
        $mStart = microtime(true);
        $result = $function();
        $mEnd = microtime(true);
        $this->resolvedOutput(
            [
                'timeDelta' => ($mEnd - $mStart),
                'elapsedTime'=>\Yii::getLogger()->elapsedTime
            ],
            __FUNCTION__ . ':' . $comment
        );
        return $result;
    }
    
    /**
     * Show execution time, memory usage, and peek memory check for specified callback
     * Return real function result
     *
     * @example
     *           $result1=$this->inspectIt(function(){
     *           $data = User::find()->where(['<','id', 100])->all();
     *           return ArrayHelper::map($data,'id','name');
     *           },'Object result');
     *           $result2 = $this->inspectIt(function(){
     *           $data = User::find()->where(['<','id', 100])->asArray()->all();
     *           $result = ArrayHelper::map($data,'id','name');
     *           unset($data);
     *           return $result;
     *           },'As Array result');
     *
     * @param \Closure $function
     * @param string   $comment Additional log comment
     *
     * @return mixed
     */
    protected function inspectIt(\Closure $function, string $comment = '')
    {
        $start = \Yii::getLogger()->elapsedTime;
        $start_memory = memory_get_usage();
        $start_peek = memory_get_peak_usage();
        $result = $function();
        $end_memory = memory_get_usage();
        $end_peek = memory_get_peak_usage();
        $end = \Yii::getLogger()->elapsedTime;
        $this->resolvedOutput(
            [
                'timeDelta' => ($end - $start),
                'memory'    => [
                    'from'                => \Yii::$app->formatter->asSize($start_memory),
                    'to'                  => \Yii::$app->formatter->asSize($end_memory),
                    'delta'               => \Yii::$app->formatter->asSize(($end_memory - $start_memory)),
                    'peekMemoryIncreased' => ($end_peek > $start_peek) ? \Yii::$app->formatter->asSize($start_peek)
                        . ' +' . \Yii::$app->formatter->asSize($end_peek - $start_peek) : 'no',
                ],
            ],
            __FUNCTION__ . ':' . $comment
        );
        return $result;
    }
    
    /**
     * Show profile log for specified callback
     * Return real function result
     *
     * @example
     *         $result=$this->profileIt(function(){
     *         $data = User::find()->where(['<','id', 100])->all();
     *         return ArrayHelper::map($data,'id','name');
     *         });
     *
     * @param \Closure $function
     * @param string   $comment Additional log comment
     *
     * @return mixed
     */
    protected function profileIt(\Closure $function, string $comment = '')
    {
        $id = uniqid('profile_');
        \Yii::beginProfile($id, __FUNCTION__ . ':' . $id);
        $result = $function();
        \Yii::endProfile($id, __FUNCTION__ . ':' . $id);
        $profile = \Yii::getLogger()->getProfiling([]);
        $map = ArrayHelper::getColumn($profile, 'info');
        $profile = array_slice($profile, array_search($id, $map));
        unset($map);
        $this->resolvedOutput($profile, __FUNCTION__ . ':' . $id . ':' . $comment);
        return $result;
    }
    
    /**
     * @param $message
     */
    protected function resolvedOutput($message, $subj = ''): void
    {
        $divider = '============= ' . $subj . ' ===============';
        if (YII_ENV_TEST === true) {
            Debug::debug($divider);
            Debug::debug($message);
        } elseif (\Yii::$app instanceof Application) {
            Console::output(Console::ansiFormat($divider . PHP_EOL, [Console::FG_BLUE]));
            Console::output(Console::ansiFormat(VarDumper::export($message) . PHP_EOL, [Console::FG_GREEN]));
        } else {
            \Yii::trace($message, get_called_class().':'.$subj);
        }
    }
   
}
<?php

use yii\db\Migration;

class m170105_004305_add_fts extends Migration
{
    public function safeUp()
    {

        /*
         * PREPARE SEARCH CONFIGURATION
         *----------------------------
         */
        $this->getDb()->createCommand(
            '
           CREATE TEXT SEARCH DICTIONARY ispell_ru (
           template  =   ispell,
           dictfile  =   ru,
           afffile   =   ru,
           stopwords =   russian
           );
           '
        )->execute();
        $this->getDb()->createCommand(
            '
           CREATE TEXT SEARCH DICTIONARY ispell_en (
           template  = ispell,
           dictfile  = en,
           afffile   = en,
           stopwords = english
           );
           '
        )->execute();
        $this->getDb()->createCommand('CREATE TEXT SEARCH CONFIGURATION ru ( COPY = russian );')->execute();
        $this->getDb()->createCommand(
            'ALTER TEXT SEARCH CONFIGURATION ru
           ALTER MAPPING
           FOR word, hword, hword_part
           WITH ispell_ru, russian_stem;
           '
        )->execute();
        $this->getDb()->createCommand(
            'ALTER TEXT SEARCH CONFIGURATION ru
           ALTER MAPPING
           FOR asciiword, asciihword, hword_asciipart
           WITH ispell_en, english_stem;'
        )->execute();
        $this->getDb()->createCommand('SET default_text_search_config = \'ru\';')->execute();
       
        /** ADD tsvector column **/
        $this->getDb()->createCommand(
            '
           ALTER TABLE {{%tovar}} ADD COLUMN fts tsvector;
        '
        )->execute();
        $this->getDb()->createCommand(
            '
           UPDATE {{%tovar}} SET fts=
setweight( coalesce( to_tsvector(\'ru\', [[name]]),\'\'),\'A\') || \' \' ||
setweight( coalesce( to_tsvector(\'ru\', [[description]]),\'\'),\'B\') || \' \';
        '
        )->execute();
        $this->getDb()->createCommand('create index fts_index on {{%tovar}} using gin (fts);')->execute();
        
        /**
         * ---   ADD AUTO FILL fts TRIGGER ON INSERT NEW RECORD
         * (in my case 'on update' trigger not neccessary)
        **/
        $this->getDb()->createCommand(
            '
            CREATE FUNCTION fts_vector_update() RETURNS TRIGGER AS $$
BEGIN
   NEW.fts=setweight( coalesce( to_tsvector(\'ru\', NEW.name),\'\'),\'A\') || \' \' ||
			setweight( coalesce( to_tsvector(\'ru\', NEW.description),\'\'),\'B\') || \' \';
			RETURN NEW;
END;
$$ LANGUAGE \'plpgsql\';
CREATE TRIGGER tovar_fts_update BEFORE INSERT ON {{%tovar}}
FOR EACH ROW EXECUTE PROCEDURE fts_vector_update();
        '
        )->execute();
    }

    public function safeDown()
    {
        $this->dropIndex('fts_index', '{{%tovar}}');
        $this->dropColumn('{{%tovar}}', 'fts');
        $this->getDb()->createCommand('DROP TRIGGER tovar_fts_update ON {{%tovar}}')->execute();
        $this->getDb()->createCommand('DROP FUNCTION IF EXISTS fts_vector_update()')->execute();
    }
}

 
    public function findSuggest(string $query, int $cat = null): array
    {
        $query = $this->prepareQuery($query);
        $tQuery = (new Query())->from('{{%tovar}}')
                               ->select([
                                   '{{%tovar}}.id',
                                   '{{%tovar}}.name',
                                   '{{%tovar}}.slug',
                                   '{{%category}}.name as category',
                                   new Expression("ts_rank({{%tovar}}.fts,plainto_tsquery('ru', :q)) as rank"),
                                        ])
                               ->leftJoin('{{%category}}','{{%tovar}}.category_id={{%category}}.id')
                               ->where(new Expression("{{%tovar}}.fts  @@ plainto_tsquery('ru', :q)", [':q' => $query]))
                               ->limit(10)
                               ->orderBy(['rank' => SORT_DESC]);
        if($cat > 0){
            $tQuery->andWhere(['{{%tovar}}.category_id'=>$cat]);
        }
        return $tQuery->all();
    }