| 1 |  |  | <?php | 
            
                                                                                                            
                            
            
                                    
            
            
                | 2 |  |  | /** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 3 |  |  |  * | 
            
                                                                                                            
                            
            
                                    
            
            
                | 4 |  |  |  * Precise Similar Topics | 
            
                                                                                                            
                            
            
                                    
            
            
                | 5 |  |  |  * | 
            
                                                                                                            
                            
            
                                    
            
            
                | 6 |  |  |  * @copyright (c) 2018 Matt Friedman | 
            
                                                                                                            
                            
            
                                    
            
            
                | 7 |  |  |  * @license GNU General Public License, version 2 (GPL-2.0) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 8 |  |  |  * | 
            
                                                                                                            
                            
            
                                    
            
            
                | 9 |  |  |  */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 10 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 11 |  |  | namespace vse\similartopics\driver; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 12 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 13 |  |  | /** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 14 |  |  |  * This class handles similar topics queries for PostgreSQL dbms | 
            
                                                                                                            
                            
            
                                    
            
            
                | 15 |  |  |  */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 16 |  |  | class postgres implements driver_interface | 
            
                                                                                                            
                            
            
                                    
            
            
                | 17 |  |  | { | 
            
                                                                                                            
                            
            
                                    
            
            
                | 18 |  |  | 	/** @var \phpbb\db\driver\driver_interface */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 19 |  |  | 	protected $db; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 20 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 21 |  |  | 	/** @var \phpbb\config\config */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 22 |  |  | 	protected $config; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 23 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 24 |  |  | 	/** @var string */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 25 |  |  | 	protected $ts_name; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 26 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 27 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 28 |  |  | 	 * Constructor | 
            
                                                                                                            
                            
            
                                    
            
            
                | 29 |  |  | 	 * | 
            
                                                                                                            
                            
            
                                    
            
            
                | 30 |  |  | 	 * @param \phpbb\db\driver\driver_interface $db | 
            
                                                                                                            
                            
            
                                    
            
            
                | 31 |  |  | 	 * @param \phpbb\config\config              $config | 
            
                                                                                                            
                            
            
                                    
            
            
                | 32 |  |  | 	 */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 33 |  |  | 	public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\config\config $config) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 34 |  |  | 	{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 35 |  |  | 		$this->db = $db; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 36 |  |  | 		$this->config = $config; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 37 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 38 |  |  | 		$this->set_ts_name($config['pst_postgres_ts_name']); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 39 |  |  | 	} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 40 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 41 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 42 |  |  | 	 * {@inheritdoc} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 43 |  |  | 	 */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 44 |  |  | 	public function get_name() | 
            
                                                                                                            
                            
            
                                    
            
            
                | 45 |  |  | 	{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 46 |  |  | 		return 'postgres'; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 47 |  |  | 	} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 48 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 49 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 50 |  |  | 	 * {@inheritdoc} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 51 |  |  | 	 */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 52 |  |  | 	public function get_type() | 
            
                                                                                                            
                            
            
                                    
            
            
                | 53 |  |  | 	{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 54 |  |  | 		return 'postgres'; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 55 |  |  | 	} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 56 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 57 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 58 |  |  | 	 * {@inheritdoc} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 59 |  |  | 	 */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 60 |  |  | 	public function get_query($topic_id, $topic_title, $length, $sensitivity) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 61 |  |  | 	{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 62 |  |  | 		$ts_name = $this->db->sql_escape($this->ts_name); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 63 |  |  | 		$ts_query_text = $this->db->sql_escape(preg_replace('/\s+/', '|', $topic_title)); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 64 |  |  | 		$ts_rank_cd = "ts_rank_cd('{1,1,1,1}', to_tsvector('$ts_name', t.topic_title), to_tsquery('$ts_name', '$ts_query_text'), 32)"; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 65 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 66 |  |  | 		return array( | 
            
                                                                                                            
                            
            
                                    
            
            
                | 67 |  |  | 			'SELECT'	=> "f.forum_id, f.forum_name, t.*, $ts_rank_cd AS score", | 
            
                                                                                                            
                            
            
                                    
            
            
                | 68 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 69 |  |  | 			'FROM'		=> array( | 
            
                                                                                                            
                            
            
                                    
            
            
                | 70 |  |  | 				TOPICS_TABLE	=> 't', | 
            
                                                                                                            
                            
            
                                    
            
            
                | 71 |  |  | 			), | 
            
                                                                                                            
                            
            
                                    
            
            
                | 72 |  |  | 			'LEFT_JOIN'	=> array( | 
            
                                                                                                            
                            
            
                                    
            
            
                | 73 |  |  | 				array( | 
            
                                                                                                            
                            
            
                                    
            
            
                | 74 |  |  | 					'FROM'	=>	array(FORUMS_TABLE	=> 'f'), | 
            
                                                                                                            
                            
            
                                    
            
            
                | 75 |  |  | 					'ON'	=> 'f.forum_id = t.forum_id', | 
            
                                                                                                            
                            
            
                                    
            
            
                | 76 |  |  | 				), | 
            
                                                                                                            
                            
            
                                    
            
            
                | 77 |  |  | 			), | 
            
                                                                                                            
                            
            
                                    
            
            
                | 78 |  |  | 			'WHERE'		=> "to_tsquery('$ts_name', '$ts_query_text') @@ to_tsvector('$ts_name', t.topic_title) AND $ts_rank_cd >= " . (float) $sensitivity . ' | 
            
                                                                                                            
                            
            
                                    
            
            
                | 79 |  |  | 				AND t.topic_status <> ' . ITEM_MOVED . ' | 
            
                                                                                                            
                            
            
                                    
            
            
                | 80 |  |  | 				AND t.topic_visibility = ' . ITEM_APPROVED . ' | 
            
                                                                                                            
                            
            
                                    
            
            
                | 81 |  |  | 				AND t.topic_time > (extract(epoch from current_timestamp)::integer - ' . (int) $length . ') | 
            
                                                                                                            
                            
            
                                    
            
            
                | 82 |  |  | 				AND t.topic_id <> ' . (int) $topic_id, | 
            
                                                                                                            
                            
            
                                    
            
            
                | 83 |  |  | 			'ORDER_BY'	=> 'score DESC, t.topic_time DESC', | 
            
                                                                                                            
                            
            
                                    
            
            
                | 84 |  |  | 		); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 85 |  |  | 	} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 86 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 87 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 88 |  |  | 	 * {@inheritdoc} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 89 |  |  | 	 */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 90 |  |  | 	public function is_supported() | 
            
                                                                                                            
                            
            
                                    
            
            
                | 91 |  |  | 	{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 92 |  |  | 		return ($this->db->get_sql_layer() === 'postgres'); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 93 |  |  | 	} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 94 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 95 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 96 |  |  | 	 * {@inheritdoc} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 97 |  |  | 	 */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 98 |  |  | 	public function is_fulltext($column = 'topic_title', $table = TOPICS_TABLE) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 99 |  |  | 	{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 100 |  |  | 		foreach ($this->get_fulltext_indexes($column, $table) as $index) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 101 |  |  | 		{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 102 |  |  | 			if ($index === $table . '_' . $this->ts_name . '_' . $column) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 103 |  |  | 			{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 104 |  |  | 				return true; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 105 |  |  | 			} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 106 |  |  | 		} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 107 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 108 |  |  | 		return false; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 109 |  |  | 	} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 110 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 111 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 112 |  |  | 	 * {@inheritdoc} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 113 |  |  | 	 */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 114 |  |  | 	public function get_fulltext_indexes($column = 'topic_title', $table = TOPICS_TABLE) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 115 |  |  | 	{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 116 |  |  | 		$indexes = array(); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 117 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 118 |  |  | 		if (!$this->is_supported()) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 119 |  |  | 		{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 120 |  |  | 			return $indexes; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 121 |  |  | 		} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 122 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 123 |  |  | 		$sql = "SELECT c2.relname | 
            
                                                                                                            
                            
            
                                    
            
            
                | 124 |  |  | 			FROM pg_catalog.pg_class c1, pg_catalog.pg_index i, pg_catalog.pg_class c2 | 
            
                                                                                                            
                            
            
                                    
            
            
                | 125 |  |  | 			WHERE c1.relname = '" . $this->db->sql_escape($table) . "' | 
            
                                                                                                            
                            
            
                                    
            
            
                | 126 |  |  | 				AND position('to_tsvector' in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)) > 0 | 
            
                                                                                                            
                            
            
                                    
            
            
                | 127 |  |  | 				AND pg_catalog.pg_table_is_visible(c1.oid) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 128 |  |  | 				AND c1.oid = i.indrelid | 
            
                                                                                                            
                            
            
                                    
            
            
                | 129 |  |  | 				AND i.indexrelid = c2.oid"; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 130 |  |  | 		$result = $this->db->sql_query($sql); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 131 |  |  | 		while ($row = $this->db->sql_fetchrow($result)) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 132 |  |  | 		{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 133 |  |  | 			if (strpos($row['relname'], $column) !== false) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 134 |  |  | 			{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 135 |  |  | 				$indexes[] = $row['relname']; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 136 |  |  | 			} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 137 |  |  | 		} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 138 |  |  | 		$this->db->sql_freeresult($result); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 139 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 140 |  |  | 		return $indexes; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 141 |  |  | 	} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 142 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 143 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 144 |  |  | 	 * {@inheritdoc} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 145 |  |  | 	 */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 146 |  |  | 	public function create_fulltext_index($column = 'topic_title', $table = TOPICS_TABLE) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 147 |  |  | 	{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 148 |  |  | 		// Make sure ts_name is current | 
            
                                                                                                            
                            
            
                                    
            
            
                | 149 |  |  | 		$this->set_ts_name($this->config['pst_postgres_ts_name']); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 150 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 151 |  |  | 		$new_index = $table . '_' . $this->ts_name . '_' . $column; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 152 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 153 |  |  | 		$indexed = false; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 154 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 155 |  |  | 		foreach ($this->get_fulltext_indexes($column, $table) as $index) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 156 |  |  | 		{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 157 |  |  | 			if ($index === $new_index) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 158 |  |  | 			{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 159 |  |  | 				$indexed = true; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 160 |  |  | 			} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 161 |  |  | 			else | 
            
                                                                                                            
                            
            
                                    
            
            
                | 162 |  |  | 			{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 163 |  |  | 				$sql = 'DROP INDEX ' . $index; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 164 |  |  | 				$this->db->sql_query($sql); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 165 |  |  | 			} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 166 |  |  | 		} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 167 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 168 |  |  | 		if (!$indexed) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 169 |  |  | 		{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 170 |  |  | 			$sql = 'CREATE INDEX ' . $this->db->sql_escape($new_index) . '  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 171 |  |  | 				ON '  . $this->db->sql_escape($table) . "  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 172 |  |  | 				USING gin (to_tsvector ('" . $this->db->sql_escape($this->ts_name) . "', " . $this->db->sql_escape($column) . '))'; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 173 |  |  | 			$this->db->sql_query($sql); | 
            
                                                                                                            
                            
            
                                    
            
            
                | 174 |  |  | 		} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 175 |  |  | 	} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 176 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 177 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 178 |  |  | 	 * {@inheritdoc} | 
            
                                                                                                            
                                                                
            
                                    
            
            
                | 179 |  |  | 	 */ | 
            
                                                                        
                            
            
                                    
            
            
                | 180 |  |  | 	public function get_engine() | 
            
                                                                        
                            
            
                                    
            
            
                | 181 |  |  | 	{ | 
            
                                                                        
                            
            
                                    
            
            
                | 182 |  |  | 		return ''; | 
            
                                                                        
                            
            
                                    
            
            
                | 183 |  |  | 	} | 
            
                                                                                                            
                            
            
                                    
            
            
                | 184 |  |  |  | 
            
                                                                                                            
                            
            
                                    
            
            
                | 185 |  |  | 	/** | 
            
                                                                                                            
                            
            
                                    
            
            
                | 186 |  |  | 	 * Set the PostgreSQL Text Search name (dictionary) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 187 |  |  | 	 * | 
            
                                                                                                            
                            
            
                                    
            
            
                | 188 |  |  | 	 * @param string $ts_name Dictionary name | 
            
                                                                                                            
                            
            
                                    
            
            
                | 189 |  |  | 	 */ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 190 |  |  | 	protected function set_ts_name($ts_name) | 
            
                                                                                                            
                            
            
                                    
            
            
                | 191 |  |  | 	{ | 
            
                                                                                                            
                            
            
                                    
            
            
                | 192 |  |  | 		$this->ts_name = $ts_name ?: 'simple'; | 
            
                                                                                                            
                            
            
                                    
            
            
                | 193 |  |  | 	} | 
            
                                                                                                            
                                                                
            
                                    
            
            
                | 194 |  |  | } | 
            
                                                        
            
                                    
            
            
                | 195 |  |  |  |