mxQrGmtw

1<?php
2
3use yii\db\Migration;
4
5class m170105_004305_add_fts extends Migration
6{
7    public function safeUp()
8    {
9
10        /*
11         * PREPARE SEARCH CONFIGURATION
12         *----------------------------
13         */
14        $this->getDb()->createCommand(
15            '
16           CREATE TEXT SEARCH DICTIONARY ispell_ru (
17           template  =   ispell,
18           dictfile  =   ru,
19           afffile   =   ru,
20           stopwords =   russian
21           );
22           '
23        )->execute();
24        $this->getDb()->createCommand(
25            '
26           CREATE TEXT SEARCH DICTIONARY ispell_en (
27           template  = ispell,
28           dictfile  = en,
29           afffile   = en,
30           stopwords = english
31           );
32           '
33        )->execute();
34        $this->getDb()->createCommand('CREATE TEXT SEARCH CONFIGURATION ru ( COPY = russian );')->execute();
35        $this->getDb()->createCommand(
36            'ALTER TEXT SEARCH CONFIGURATION ru
37           ALTER MAPPING
38           FOR word, hword, hword_part
39           WITH ispell_ru, russian_stem;
40           '
41        )->execute();
42        $this->getDb()->createCommand(
43            'ALTER TEXT SEARCH CONFIGURATION ru
44           ALTER MAPPING
45           FOR asciiword, asciihword, hword_asciipart
46           WITH ispell_en, english_stem;'
47        )->execute();
48        $this->getDb()->createCommand('SET default_text_search_config = \'ru\';')->execute();
49       
50        /** ADD tsvector column **/
51        $this->getDb()->createCommand(
52            '
53           ALTER TABLE {{%tovar}} ADD COLUMN fts tsvector;
54        '
55        )->execute();
56        $this->getDb()->createCommand(
57            '
58           UPDATE {{%tovar}} SET fts=
59setweight( coalesce( to_tsvector(\'ru\', [[name]]),\'\'),\'A\') || \' \' ||
60setweight( coalesce( to_tsvector(\'ru\', [[description]]),\'\'),\'B\') || \' \';
61        '
62        )->execute();
63        $this->getDb()->createCommand('create index fts_index on {{%tovar}} using gin (fts);')->execute();
64        
65        /**
66         * ---   ADD AUTO FILL fts TRIGGER ON INSERT NEW RECORD
67         * (in my case 'on update' trigger not neccessary)
68        **/
69        $this->getDb()->createCommand(
70            '
71            CREATE FUNCTION fts_vector_update() RETURNS TRIGGER AS $$
72BEGIN
73   NEW.fts=setweight( coalesce( to_tsvector(\'ru\', NEW.name),\'\'),\'A\') || \' \' ||
74			setweight( coalesce( to_tsvector(\'ru\', NEW.description),\'\'),\'B\') || \' \';
75			RETURN NEW;
76END;
77$$ LANGUAGE \'plpgsql\';
78CREATE TRIGGER tovar_fts_update BEFORE INSERT ON {{%tovar}}
79FOR EACH ROW EXECUTE PROCEDURE fts_vector_update();
80        '
81        )->execute();
82    }
83
84    public function safeDown()
85    {
86        $this->dropIndex('fts_index', '{{%tovar}}');
87        $this->dropColumn('{{%tovar}}', 'fts');
88        $this->getDb()->createCommand('DROP TRIGGER tovar_fts_update ON {{%tovar}}')->execute();
89        $this->getDb()->createCommand('DROP FUNCTION IF EXISTS fts_vector_update()')->execute();
90    }
91}