Completed
Push — master ( 52e9e1...4faf8b )
by Matt
9s
created

postgres::get_engine()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 4
Code Lines 2

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
dl 0
loc 4
rs 10
c 0
b 0
f 0
cc 1
eloc 2
nc 1
nop 0
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