postgres::get_name() - Code Metrics - Inspection of "PostgreSQL support" - iMattPro/similartopics - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#25)

by Matt

created 2018-03-11 08:06 UTC

postgres::get_name() A

↳ Parent: postgres

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	4
rs	10
c	0
b	0
f	0
cc	1
eloc	2
nc	1
nop	0

<?php
/**
 *
 * Precise Similar Topics
 *
 * @copyright (c) 2018 Matt Friedman
 * @license GNU General Public License, version 2 (GPL-2.0)
 *
 */

namespace vse\similartopics\driver;

class postgres implements driver_interface
{
	/** @var \phpbb\db\driver\driver_interface */
	protected $db;

	/** @var string */
	protected $ts_name;

	/**
	 * mysql constructor.
	 *
	 * @param \phpbb\db\driver\driver_interface $db
	 * @param \phpbb\config\config              $config
	 */
	public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\config\config $config)
	{
		$this->db = $db;
		$this->set_ts_name($config['pst_postgres_ts_name']);
	}

	/**
	 * {@inheritdoc}
	 */
	public function get_name()
	{
		return 'postgres';
	}

	/**
	 * {@inheritdoc}
	 */
	public function get_type()
	{
		return 'postgres';
	}

	/**
	 * {@inheritdoc}
	 */
	public function get_query($topic_id, $topic_title, $length, $sensitivity)
	{
		$ts_query_text = $this->db->sql_escape(str_replace(' ', '|', $topic_title));
		$ts_name = $this->db->sql_escape($this->ts_name);

		return array(
			'SELECT'	=> "f.forum_id, f.forum_name, t.*,
				ts_rank_cd(to_tsvector('$ts_name', t.topic_title), to_tsquery('$ts_query_text'), 32) AS score",

			'FROM'		=> array(
				TOPICS_TABLE	=> 't',
			),
			'LEFT_JOIN'	=> array(
				array(
					'FROM'	=>	array(FORUMS_TABLE	=> 'f'),
					'ON'	=> 'f.forum_id = t.forum_id',
				),
			),
			'WHERE'		=> "ts_rank_cd(to_tsvector('$ts_name', t.topic_title), to_tsquery('$ts_query_text'), 32) >= " . (float) $sensitivity . '
				AND t.topic_status <> ' . ITEM_MOVED . '
				AND t.topic_visibility = ' . ITEM_APPROVED . '
				AND t.topic_time > (extract(epoch from current_timestamp)::integer - ' . (int) $length . ')
				AND t.topic_id <> ' . (int) $topic_id,
			'ORDER_BY'	=> 'score DESC, t.topic_time DESC',
		);
	}

	/**
	 * {@inheritdoc}
	 */
	public function is_supported()
	{
		return ($this->db->get_sql_layer() === 'postgres');
	}

	/**
	 * {@inheritdoc}
	 */
	public function is_index($column = 'topic_title')
	{
		$is_index = false;

		foreach ($this->get_pg_indexes($column) as $index)
		{
			if ($index === TOPICS_TABLE . '_' . $this->ts_name . '_' . $column)
			{
				$is_index = true;
				break;
			}
		}

		return $is_index;
	}

	/**
	 * {@inheritdoc}
	 */
	public function create_fulltext_index($column = 'topic_title')
	{
		$new_index = TOPICS_TABLE . '_' . $this->ts_name . '_' . $column;

		$indexed = false;

		foreach ($this->get_pg_indexes() as $index)
		{
			if ($index === $new_index)
			{
				$indexed = true;
			}
			else
			{
				$sql = 'DROP INDEX ' . $index;
				$this->db->sql_query($sql);
			}
		}

		if (!$indexed)
		{
			$sql = 'CREATE INDEX ' . $this->db->sql_escape($new_index) . ' 
				ON '  . TOPICS_TABLE . " 
				USING gin (to_tsvector ('" . $this->db->sql_escape($this->ts_name) . "', " . $this->db->sql_escape($column) . '))';
			$this->db->sql_query($sql);
		}
	}

	/**
	 * Set the PostgreSQL Text Search name (dictionary)
	 *
	 * @param string $ts_name Dictionary name
	 * @return \vse\similartopics\driver\postgres
	 */
	public function set_ts_name($ts_name)
	{
		$this->ts_name = $ts_name ?: 'simple';

		return $this;
	}

	/**
	 * get all PostgreSQL FULLTEXT indexes on field in topics table
	 *
	 * @access public
	 * @param string $column name of a field
	 * @return array contains index names
	 */
	public function get_pg_indexes($column = 'topic_title')
	{
		$indexes = array();

		if (!$this->is_supported())
		{
			return $indexes;
		}

		$sql = "SELECT c2.relname, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS indexdef
			FROM pg_catalog.pg_class c1, pg_catalog.pg_index i, pg_catalog.pg_class c2
			WHERE c1.relname = '" . TOPICS_TABLE . "'
				AND pg_catalog.pg_table_is_visible(c1.oid)
				AND c1.oid = i.indrelid
				AND i.indexrelid = c2.oid";
		$result = $this->db->sql_query($sql);

		while ($row = $this->db->sql_fetchrow($result))
		{
			if (strpos($row['relname'], $column) !== false)
			{
				$indexes[] = $row['relname'];
			}
		}
		$this->db->sql_freeresult($result);

		return $indexes;
	}

	/**
	 * Get list of PostgreSQL text search names
	 *
	 * @return array array of text search names
	 */
	public function get_cfgname_list()
	{
		$sql = 'SELECT cfgname AS ts_name FROM pg_ts_config';
		$result = $this->db->sql_query($sql);
		$ts_options = $this->db->sql_fetchrowset($result);
		$this->db->sql_freeresult($result);

		return $ts_options;
	}
}


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			class postgres implements driver_interface
14			{
15			/** @var \phpbb\db\driver\driver_interface */
16			protected $db;
17
18			/** @var string */
19			protected $ts_name;
20
21			/**
22			* mysql constructor.
23			*
24			* @param \phpbb\db\driver\driver_interface $db
25			* @param \phpbb\config\config $config
26			*/
27			public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\config\config $config)
28			{
29			$this->db = $db;
30			$this->set_ts_name($config['pst_postgres_ts_name']);
31			}
32
33			/**
34			* {@inheritdoc}
35			*/
36			public function get_name()
37			{
38			return 'postgres';
39			}
40
41			/**
42			* {@inheritdoc}
43			*/
44			public function get_type()
45			{
46			return 'postgres';
47			}
48
49			/**
50			* {@inheritdoc}
51			*/
52			public function get_query($topic_id, $topic_title, $length, $sensitivity)
53			{
54			$ts_query_text = $this->db->sql_escape(str_replace(' ', '\|', $topic_title));
55			$ts_name = $this->db->sql_escape($this->ts_name);
56
57			return array(
58			'SELECT' => "f.forum_id, f.forum_name, t.*,
59			ts_rank_cd(to_tsvector('$ts_name', t.topic_title), to_tsquery('$ts_query_text'), 32) AS score",
60
61			'FROM' => array(
62			TOPICS_TABLE => 't',
63			),
64			'LEFT_JOIN' => array(
65			array(
66			'FROM' => array(FORUMS_TABLE => 'f'),
67			'ON' => 'f.forum_id = t.forum_id',
68			),
69			),
70			'WHERE' => "ts_rank_cd(to_tsvector('$ts_name', t.topic_title), to_tsquery('$ts_query_text'), 32) >= " . (float) $sensitivity . '
71			AND t.topic_status <> ' . ITEM_MOVED . '
72			AND t.topic_visibility = ' . ITEM_APPROVED . '
73			AND t.topic_time > (extract(epoch from current_timestamp)::integer - ' . (int) $length . ')
74			AND t.topic_id <> ' . (int) $topic_id,
75			'ORDER_BY' => 'score DESC, t.topic_time DESC',
76			);
77			}
78
79			/**
80			* {@inheritdoc}
81			*/
82			public function is_supported()
83			{
84			return ($this->db->get_sql_layer() === 'postgres');
85			}
86
87			/**
88			* {@inheritdoc}
89			*/
90			public function is_index($column = 'topic_title')
91			{
92			$is_index = false;
93
94			foreach ($this->get_pg_indexes($column) as $index)
95			{
96			if ($index === TOPICS_TABLE . '_' . $this->ts_name . '_' . $column)
97			{
98			$is_index = true;
99			break;
100			}
101			}
102
103			return $is_index;
104			}
105
106			/**
107			* {@inheritdoc}
108			*/
109			public function create_fulltext_index($column = 'topic_title')
110			{
111			$new_index = TOPICS_TABLE . '_' . $this->ts_name . '_' . $column;
112
113			$indexed = false;
114
115			foreach ($this->get_pg_indexes() as $index)
116			{
117			if ($index === $new_index)
118			{
119			$indexed = true;
120			}
121			else
122			{
123			$sql = 'DROP INDEX ' . $index;
124			$this->db->sql_query($sql);
125			}
126			}
127
128			if (!$indexed)
129			{
130			$sql = 'CREATE INDEX ' . $this->db->sql_escape($new_index) . '
131			ON ' . TOPICS_TABLE . "
132			USING gin (to_tsvector ('" . $this->db->sql_escape($this->ts_name) . "', " . $this->db->sql_escape($column) . '))';
133			$this->db->sql_query($sql);
134			}
135			}
136
137			/**
138			* Set the PostgreSQL Text Search name (dictionary)
139			*
140			* @param string $ts_name Dictionary name
141			* @return \vse\similartopics\driver\postgres
142			*/
143			public function set_ts_name($ts_name)
144			{
145			$this->ts_name = $ts_name ?: 'simple';
146
147			return $this;
148			}
149
150			/**
151			* get all PostgreSQL FULLTEXT indexes on field in topics table
152			*
153			* @access public
154			* @param string $column name of a field
155			* @return array contains index names
156			*/
157			public function get_pg_indexes($column = 'topic_title')
158			{
159			$indexes = array();
160
161			if (!$this->is_supported())
162			{
163			return $indexes;
164			}
165
166			$sql = "SELECT c2.relname, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS indexdef
167			FROM pg_catalog.pg_class c1, pg_catalog.pg_index i, pg_catalog.pg_class c2
168			WHERE c1.relname = '" . TOPICS_TABLE . "'
169			AND pg_catalog.pg_table_is_visible(c1.oid)
170			AND c1.oid = i.indrelid
171			AND i.indexrelid = c2.oid";
172			$result = $this->db->sql_query($sql);
173
174			while ($row = $this->db->sql_fetchrow($result))
175			{
176			if (strpos($row['relname'], $column) !== false)
177			{
178			$indexes[] = $row['relname'];
179			}
180			}
181			$this->db->sql_freeresult($result);
182
183			return $indexes;
184			}
185
186			/**
187			* Get list of PostgreSQL text search names
188			*
189			* @return array array of text search names
190			*/
191			public function get_cfgname_list()
192			{
193			$sql = 'SELECT cfgname AS ts_name FROM pg_ts_config';
194			$result = $this->db->sql_query($sql);
195			$ts_options = $this->db->sql_fetchrowset($result);
196			$this->db->sql_freeresult($result);
197
198			return $ts_options;
199			}
200			}
201

iMattPro / similartopics

Pull Request — master (#25)

postgres::get_name() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like