builder::set_sql_where() - Code Metrics - Inspection of "Fix type casting bugs" - blitze/phpBB-ext-sitemaker - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — develop ( b63a8c...2b5dac )

by Daniel

created 2019-04-01 06:03 UTC

builder::set_sql_where() A

↳ Parent: builder

Complexity

Conditions	1
Paths	1

Size

Total Lines	5
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	4
CRAP Score	1

Importance

Changes

Metric	Value
cc	1
eloc	2
nc	1
nop	1
dl	0
loc	5
ccs	4
cts	4
cp	1
crap	1
rs	10
c	0
b	0
f	0

<?php
/**
 *
 * @package sitemaker
 * @copyright (c) 2013 Daniel A. (blitze)
 * @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License v2
 *
 */

namespace blitze\sitemaker\services\tree;

/**
* Manage nested sets
*/
abstract class builder extends \phpbb\tree\nestedset
{
	/**
	 * Set additional sql where restrictions
	 * @param string $sql_where
	 * @return $this
	 */
	public function set_sql_where($sql_where)
	{
		$this->sql_where = $sql_where;

		return $this;
	}

	/**
	 * Get item data
	 * @param int $item_id
	 * @return mixed
	 */
	public function get_item_info($item_id = 0)
	{
		$sql = 'SELECT * FROM ' . $this->table_name . ' ' . $this->get_sql_where('WHERE') .
			(($item_id) ? " AND {$this->column_item_id} = " . (int) $item_id : '');

		$result = $this->db->sql_query($sql);
		$row = $this->db->sql_fetchrow($result);
		$this->db->sql_freeresult($result);

		return $row;
	}

	/**
	 * Update a single item
	 *
	 * @param	integer	$item_id		The ID of the item to update.
	 * @param	array	$sql_data		Other item attributes to insert in the database ex. array('title' => 'Item 1')
	 * @return	array
	 */
	public function update_item($item_id, array $sql_data)
	{
		$sql = "UPDATE {$this->table_name}
			SET " . $this->db->sql_build_array('UPDATE', $sql_data) . "
			WHERE $this->column_item_id = " . (int) $item_id;
		$this->db->sql_query($sql);

		return $sql_data;
	}

	/**
	 * Update tree
	 *
	 * @param	array	$tree 	Array of parent-child items
	 * @return	array
	 */
	public function update_tree(array $tree)
	{
		$items_data = $this->get_all_tree_data();

		/**
		 * Remove any new nodes in the tree that did not exist before
		 * The intent of this method is to update an existing tree, not add new nodes
		 */
		$tree = array_intersect_key($tree, $items_data);

		// we do it this way because array_merge_recursive, would append numeric keys rather than overwrite them
		foreach ($tree as $key => $data)
		{
			$tree[$key] = array_merge($items_data[$key], $data);
		}

		$this->acquire_lock();
		$this->db->sql_transaction('begin');

		// Rather than updating each item individually, we will just delete all items
		// then add them all over again with new parent_id|left_id|right_id
		$this->db->sql_query("DELETE FROM {$this->table_name} " . $this->get_sql_where('WHERE'));

		// Now we add it back
		$sql_data = $this->add_sub_tree($tree, 0);

		$this->db->sql_transaction('commit');
		$this->lock->release();

		return $sql_data;
	}

	/**
	 * Takes a new line delimited string and returns an associative array of parent/child relationships
	 * that can be saved to a database or converted to a nested set model
	 *
	 * @param	string	$structure		The structure to build ex:
	 *										Home|index.php
	 *										News|index.php?p=news
	 *											Texas|index.php?p=news&cat=Texas
	 *										About Us|index.php?p=about
	 * @param	array	$table_fields	The expected information to get for each line (order is important) ex: array('title' => '', 'url' => '')
	 *									This will then assign 'Home' to 'title' and 'index.php' to 'url' in example above (line 1)
	 * @param array     $data
	 * @return	array					associative array of parent/child relationships ex: the above examples will produce
	 *										array(
	 *											1   => array('title' => 'Home', 'url' => 'index.php', parent_id => 0),
	 *											2   => array('title' => 'News', 'url' => 'index.php?p=news', parent_id => 0),
	 *											3   => array('title' => 'Texas', 'url' => 'index.php?p=news&cat=Texas', parent_id => 2),
	 *											4   => array('title' => 'About Us', 'url' => 'index.php?p=about', parent_id => 0),
	 *										)
	 */
	public function string_to_nestedset($structure, array $table_fields, $data = array())
	{
		$field_size = sizeof($table_fields);
		$fields = array_keys($table_fields);
		$values = array_fill(0, $field_size, '');
		$lines = array_filter(explode("\n", $structure));
		$max_id = $this->get_max_id($this->column_item_id, false);

		$adj_tree = $parent_ary = array();
		foreach ($lines as $i => $string)
		{
			$depth = strspn($string, "\t");
			$parent_id = (isset($parent_ary[$depth - 1])) ? $parent_ary[$depth - 1] : 0;

			if ($depth && !$parent_id)
			{
				throw new \RuntimeException($this->message_prefix . 'MALFORMED_TREE');
			}

			$key = $i + $max_id + 1;
			$field_values = array_map('trim', explode('|', trim($string))) + $values;

			$adj_tree[$key] = array_merge($data, $this->get_item_props($fields, $field_values));
			$adj_tree[$key][$this->column_item_id] = $key;
			$adj_tree[$key]['parent_id'] = $parent_id;

			$parent_ary[$depth] = $key;
		}

		return $adj_tree;
	}

	/**
	 * @param array $fields
	 * @param array $values
	 * @return array
	 */
	protected function get_item_props(array $fields, array $values)
	{
		if (($props = array_combine($fields, $values)) !== false)
		{
			return $props;
		}
		return array();
	}

	/**
	 * Adds a new branch to the tree from an array
	 *
	 * @param	array	$branch		Array of nodes to add to tree of form:
	 * 									array(
	 * 										1   => array('title' => 'Home', 'url' => 'index.php', parent_id => 0),
	 * 										2   => array('title' => 'News', 'url' => 'index.php?p=news', parent_id => 0),
	 * 										3   => array('title' => 'Texas', 'url' => 'index.php?p=news&cat=Texas', parent_id => 2),
	 * 										4   => array('title' => 'About Us', 'url' => 'index.php?p=about', parent_id => 0),
	 * 									);
	 * @param	int		$parent_id  Parent id of the branch we're adding
	 * @return	int[]	ids of newly added items
	 */
	public function add_branch(array $branch, $parent_id = 0)
	{
		$this->acquire_lock();
		$this->db->sql_transaction('begin');

		$this->add_sub_tree($branch, $parent_id);

		$this->db->sql_transaction('commit');
		$this->lock->release();

		return array_keys($branch);
	}

	/**
	 * @param array $branch
	 * @param int   $parent_id
	 * @return mixed
	 */
	protected function add_sub_tree(array $branch, $parent_id = 0)
	{
		$sql_data = $this->prepare_branch($parent_id, $branch);

		$this->db->sql_multi_insert($this->table_name, $sql_data);

		return $sql_data;
	}

	/**
	 * @param int   $parent_id
	 * @param array $branch
	 * @return array
	 */
	protected function prepare_branch($parent_id, array $branch)
	{
		$starting_data = $this->get_starting_data($parent_id, $branch);

		$depth = $starting_data['depth'];
		$right_id = $starting_data['right_id'];

		$sql_data = array();
		foreach ($branch as $i => $row)
		{
			$left_id	= $right_id + 1;
			$right_id   = $right_id + 2;

			$sql_data[$i] = $row;
			$sql_data[$i]['parent_id']	= $parent_id;
			$sql_data[$i]['left_id']	= $left_id;
			$sql_data[$i]['right_id']	= $right_id;
			$sql_data[$i]['depth']		= $depth;

			if ($row['parent_id'])
			{
				$left_id	= $sql_data[$row['parent_id']]['right_id'];
				$right_id   = $left_id + 1;

				$sql_data[$i]['parent_id']	= $row['parent_id'];
				$sql_data[$i]['depth']		= $sql_data[$row['parent_id']]['depth'] + 1;
				$sql_data[$i]['left_id']	= $left_id;
				$sql_data[$i]['right_id']	= $right_id;

				$this->update_right_side($sql_data, $right_id, $row['parent_id'], $branch);
			}
		}

		return array_values($sql_data);
	}

	/**
	 * @param int   $parent_id
	 * @param array $branch
	 * @return array
	 * @throws \RuntimeException
	 */
	protected function get_starting_data($parent_id, array $branch)
	{
		if ($parent_id)
		{
			$new_parent = $this->get_item_info($parent_id);

			if (!$new_parent)
			{
				$this->db->sql_transaction('rollback');
				$this->lock->release();

				throw new \RuntimeException($this->message_prefix . 'INVALID_PARENT');
			}

			// adjust items in affected branch
			$this->prepare_adding_subset(array_keys($branch), $new_parent);

			return array(
				'depth'		=> $new_parent['depth'] + 1,
				'right_id'	=> --$new_parent['right_id'],
			);
		}
		else
		{
			return array(
				'depth'		=> 0,
				'right_id'	=> $this->get_max_id($this->column_right_id),
			);
		}
	}

	/**
	 * @param string $column
	 * @param bool   $use_sql_where
	 * @return int|mixed
	 */
	protected function get_max_id($column, $use_sql_where = true)
	{
		$sql = "SELECT MAX($column) AS $column
			FROM {$this->table_name} " .
			(($use_sql_where) ? $this->get_sql_where('WHERE') : '');
		$result = $this->db->sql_query($sql);
		$max_id = $this->db->sql_fetchfield($column);
		$this->db->sql_freeresult($result);

		return ($max_id) ? $max_id : 0;
	}

	/**
	 * Update right side of tree
	 * @param array $data
	 * @param int   $right_id
	 * @param int   $index
	 * @param array $branch
	 */
	protected function update_right_side(array &$data, &$right_id, $index, array $branch)
	{
		$right_id++;
		$data[$index]['right_id'] = $right_id;

		if ($branch[$index]['parent_id'])
		{
			$this->update_right_side($data, $right_id, $branch[$index]['parent_id'], $branch);
		}
	}
}


1		<?php
2		/**
3		*
4		* @package sitemaker
5		* @copyright (c) 2013 Daniel A. (blitze)
6		* @license http://opensource.org/licenses/gpl-2.0.php GNU General Public License v2
7		*
8		*/
9
10		namespace blitze\sitemaker\services\tree;
11
12		/**
13		* Manage nested sets
14		*/
15		abstract class builder extends \phpbb\tree\nestedset
16		{
17		/**
18		* Set additional sql where restrictions
19		* @param string $sql_where
20	2	* @return $this
21		*/
22	2	public function set_sql_where($sql_where)
23		{
24	2	$this->sql_where = $sql_where;
25	2
26	2	return $this;
27	2	}
28	2
29	2	/**
30	2	* Get item data
31	2	* @param int $item_id
32	2	* @return mixed
33		*/
34	2	public function get_item_info($item_id = 0)
35	2	{
36	2	$sql = 'SELECT * FROM ' . $this->table_name . ' ' . $this->get_sql_where('WHERE') .
37	2	(($item_id) ? " AND {$this->column_item_id} = " . (int) $item_id : '');
38
39	2	$result = $this->db->sql_query($sql);
40	2	$row = $this->db->sql_fetchrow($result);
41		$this->db->sql_freeresult($result);
42
43		return $row;
44		}
45
46		/**
47	50	* Update a single item
48		*
49	50	* @param integer $item_id The ID of the item to update.
50		* @param array $sql_data Other item attributes to insert in the database ex. array('title' => 'Item 1')
51	50	* @return array
52		*/
53		public function update_item($item_id, array $sql_data)
54		{
55		$sql = "UPDATE {$this->table_name}
56		SET " . $this->db->sql_build_array('UPDATE', $sql_data) . "
57		WHERE $this->column_item_id = " . (int) $item_id;
58		$this->db->sql_query($sql);
59	14
60		return $sql_data;
61	14	}
62	14
63		/**
64	14	* Update tree
65	14	*
66	14	* @param array $tree Array of parent-child items
67		* @return array
68	14	*/
69		public function update_tree(array $tree)
70		{
71		$items_data = $this->get_all_tree_data();
72
73		/**
74		* Remove any new nodes in the tree that did not exist before
75		* The intent of this method is to update an existing tree, not add new nodes
76		*/
77		$tree = array_intersect_key($tree, $items_data);
78	4
79		// we do it this way because array_merge_recursive, would append numeric keys rather than overwrite them
80	4	foreach ($tree as $key => $data)
81	4	{
82	4	$tree[$key] = array_merge($items_data[$key], $data);
83	4	}
84
85	4	$this->acquire_lock();
86		$this->db->sql_transaction('begin');
87
88		// Rather than updating each item individually, we will just delete all items
89		// then add them all over again with new parent_id\|left_id\|right_id
90		$this->db->sql_query("DELETE FROM {$this->table_name} " . $this->get_sql_where('WHERE'));
91
92		// Now we add it back
93		$sql_data = $this->add_sub_tree($tree, 0);
94	3
95		$this->db->sql_transaction('commit');
96	3	$this->lock->release();
97
98		return $sql_data;
99		}
100
101		/**
102	3	* Takes a new line delimited string and returns an associative array of parent/child relationships
103		* that can be saved to a database or converted to a nested set model
104		*
105	3	* @param string $structure The structure to build ex:
106		* Home\|index.php
107	3	* News\|index.php?p=news
108	3	* Texas\|index.php?p=news&cat=Texas
109		* About Us\|index.php?p=about
110	3	* @param array $table_fields The expected information to get for each line (order is important) ex: array('title' => '', 'url' => '')
111	3	* This will then assign 'Home' to 'title' and 'index.php' to 'url' in example above (line 1)
112		* @param array $data
113		* @return array associative array of parent/child relationships ex: the above examples will produce
114		* array(
115	3	* 1 => array('title' => 'Home', 'url' => 'index.php', parent_id => 0),
116		* 2 => array('title' => 'News', 'url' => 'index.php?p=news', parent_id => 0),
117		* 3 => array('title' => 'Texas', 'url' => 'index.php?p=news&cat=Texas', parent_id => 2),
118	3	* 4 => array('title' => 'About Us', 'url' => 'index.php?p=about', parent_id => 0),
119		* )
120	3	*/
121	3	public function string_to_nestedset($structure, array $table_fields, $data = array())
122		{
123	3	$field_size = sizeof($table_fields);
124		$fields = array_keys($table_fields);
125		$values = array_fill(0, $field_size, '');
126		$lines = array_filter(explode("\n", $structure));
127		$max_id = $this->get_max_id($this->column_item_id, false);
128
129		$adj_tree = $parent_ary = array();
130		foreach ($lines as $i => $string)
131		{
132		$depth = strspn($string, "\t");
133		$parent_id = (isset($parent_ary[$depth - 1])) ? $parent_ary[$depth - 1] : 0;
134
135		if ($depth && !$parent_id)
136		{
137		throw new \RuntimeException($this->message_prefix . 'MALFORMED_TREE');
138		}
139
140		$key = $i + $max_id + 1;
141		$field_values = array_map('trim', explode('\|', trim($string))) + $values;
142
143		$adj_tree[$key] = array_merge($data, $this->get_item_props($fields, $field_values));
144		$adj_tree[$key][$this->column_item_id] = $key;
145		$adj_tree[$key]['parent_id'] = $parent_id;
146	7
147		$parent_ary[$depth] = $key;
148	7	}
149	7
150	7	return $adj_tree;
151	7	}
152	7
153		/**
154	7	* @param array $fields
155	7	* @param array $values
156		* @return array
157	7	*/
158	7	protected function get_item_props(array $fields, array $values)
159		{
160	7	if (($props = array_combine($fields, $values)) !== false)
161	7	{
162	1	return $props;
163		}
164		return array();
165	6	}
166	6
167		/**
168	6	* Adds a new branch to the tree from an array
169	6	*
170	6	* @param array $branch Array of nodes to add to tree of form:
171		* array(
172	6	* 1 => array('title' => 'Home', 'url' => 'index.php', parent_id => 0),
173	6	* 2 => array('title' => 'News', 'url' => 'index.php?p=news', parent_id => 0),
174		* 3 => array('title' => 'Texas', 'url' => 'index.php?p=news&cat=Texas', parent_id => 2),
175	6	* 4 => array('title' => 'About Us', 'url' => 'index.php?p=about', parent_id => 0),
176		* );
177		* @param int $parent_id Parent id of the branch we're adding
178		* @return int[] ids of newly added items
179		*/
180		public function add_branch(array $branch, $parent_id = 0)
181		{
182		$this->acquire_lock();
183		$this->db->sql_transaction('begin');
184
185		$this->add_sub_tree($branch, $parent_id);
186
187		$this->db->sql_transaction('commit');
188		$this->lock->release();
189
190		return array_keys($branch);
191	6	}
192
193	6	/**
194	6	* @param array $branch
195		* @param int $parent_id
196	6	* @return mixed
197		*/
198	5	protected function add_sub_tree(array $branch, $parent_id = 0)
199	5	{
200		$sql_data = $this->prepare_branch($parent_id, $branch);
201	5
202		$this->db->sql_multi_insert($this->table_name, $sql_data);
203
204		return $sql_data;
205		}
206
207		/**
208		* @param int $parent_id
209	9	* @param array $branch
210		* @return array
211	9	*/
212		protected function prepare_branch($parent_id, array $branch)
213	8	{
214		$starting_data = $this->get_starting_data($parent_id, $branch);
215	8
216		$depth = $starting_data['depth'];
217		$right_id = $starting_data['right_id'];
218
219		$sql_data = array();
220		foreach ($branch as $i => $row)
221		{
222		$left_id = $right_id + 1;
223	9	$right_id = $right_id + 2;
224
225	9	$sql_data[$i] = $row;
226		$sql_data[$i]['parent_id'] = $parent_id;
227	8	$sql_data[$i]['left_id'] = $left_id;
228	8	$sql_data[$i]['right_id'] = $right_id;
229		$sql_data[$i]['depth'] = $depth;
230	8
231	8	if ($row['parent_id'])
232		{
233	8	$left_id = $sql_data[$row['parent_id']]['right_id'];
234	8	$right_id = $left_id + 1;
235
236	8	$sql_data[$i]['parent_id'] = $row['parent_id'];
237	8	$sql_data[$i]['depth'] = $sql_data[$row['parent_id']]['depth'] + 1;
238	8	$sql_data[$i]['left_id'] = $left_id;
239	8	$sql_data[$i]['right_id'] = $right_id;
240	8
241		$this->update_right_side($sql_data, $right_id, $row['parent_id'], $branch);
242	8	}
243	8	}
244	7
245	7	return array_values($sql_data);
246		}
247	7
248	7	/**
249	7	* @param int $parent_id
250	7	* @param array $branch
251		* @return array
252	7	* @throws \RuntimeException
253	7	*/
254	8	protected function get_starting_data($parent_id, array $branch)
255		{
256	8	if ($parent_id)
257		{
258		$new_parent = $this->get_item_info($parent_id);
259
260		if (!$new_parent)
261		{
262		$this->db->sql_transaction('rollback');
263		$this->lock->release();
264
265	9	throw new \RuntimeException($this->message_prefix . 'INVALID_PARENT');
266		}
267
268	9	// adjust items in affected branch
269	3	$this->prepare_adding_subset(array_keys($branch), $new_parent);
270
271	3	return array(
272	3	'depth' => $new_parent['depth'] + 1,
273	1	'right_id' => --$new_parent['right_id'],
274	1	);
275		}
276	1	else
277		{
278		return array(
279		'depth' => 0,
280	2	'right_id' => $this->get_max_id($this->column_right_id),
281		);
282		}
283	2	}
284	2
285	2	/**
286		* @param string $column
287		* @param bool $use_sql_where
288		* @return int\|mixed
289		*/
290	6	protected function get_max_id($column, $use_sql_where = true)
291	6	{
292	6	$sql = "SELECT MAX($column) AS $column
293		FROM {$this->table_name} " .
294		(($use_sql_where) ? $this->get_sql_where('WHERE') : '');
295		$result = $this->db->sql_query($sql);
296		$max_id = $this->db->sql_fetchfield($column);
297		$this->db->sql_freeresult($result);
298
299		return ($max_id) ? $max_id : 0;
300		}
301	10
302		/**
303	10	* Update right side of tree
304	10	* @param array $data
305	10	* @param int $right_id
306	10	* @param int $index
307	10	* @param array $branch
308	10	*/
309		protected function update_right_side(array &$data, &$right_id, $index, array $branch)
310	10	{
311		$right_id++;
312		$data[$index]['right_id'] = $right_id;
313
314		if ($branch[$index]['parent_id'])
315		{
316		$this->update_right_side($data, $right_id, $branch[$index]['parent_id'], $branch);
317		}
318		}
319		}
320

blitze / phpBB-ext-sitemaker

Push — develop ( b63a8c...2b5dac )

builder::set_sql_where() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like