MySQLConvertOldIp()

Complexity

Conditions	12
Paths	86

Size

Total Lines	120
Code Lines	67

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
function MySQLConvertOldIp($targetTable, $oldCol, $newCol, $limit = 50000, $setSize = 100)
{
	global $smcFunc, $step_progress;

	$current_substep = !isset($_GET['substep']) ? 0 : (int) $_GET['substep'];

	// Skip this if we don't have the column
	$request = $smcFunc['db_query']('', '
		SHOW FIELDS
		FROM {db_prefix}{raw:table}
		WHERE Field = {string:name}',
		array(
			'table' => $targetTable,
			'name' => $oldCol,
		)
	);
	if ($smcFunc['db_num_rows']($request) !== 1)
	{
		$smcFunc['db_free_result']($request);
		return;
	}
	$smcFunc['db_free_result']($request);

	// Setup progress bar
	if (!isset($_GET['total_fixes']) || !isset($_GET['a']))
	{
		$request = $smcFunc['db_query']('', '
			SELECT COUNT(DISTINCT {raw:old_col})
			FROM {db_prefix}{raw:table_name}',
			array(
				'old_col' => $oldCol,
				'table_name' => $targetTable,
			)
		);
		list ($step_progress['total']) = $smcFunc['db_fetch_row']($request);
		$_GET['total_fixes'] = $step_progress['total'];
		$smcFunc['db_free_result']($request);

		$_GET['a'] = 0;
	}

	$step_progress['name'] = 'Converting ips';
	$step_progress['current'] = $_GET['a'];
	$step_progress['total'] = $_GET['total_fixes'];

	// Main process loop
	$is_done = false;
	while (!$is_done)
	{
		// Keep looping at the current step.
		nextSubstep($current_substep);

		// mysql default max length is 1mb https://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
		$arIp = array();

		$request = $smcFunc['db_query']('', '
			SELECT DISTINCT {raw:old_col}
			FROM {db_prefix}{raw:table_name}
			WHERE {raw:new_col} = {string:empty}
			LIMIT {int:limit}',
			array(
				'old_col' => $oldCol,
				'new_col' => $newCol,
				'table_name' => $targetTable,
				'empty' => '',
				'limit' => $limit,
			)
		);
		while ($row = $smcFunc['db_fetch_assoc']($request))
			$arIp[] = $row[$oldCol];

		$smcFunc['db_free_result']($request);

		if (empty($arIp))
			$is_done = true;

		$updates = array();
		$new_ips = array();
		$cases = array();
		$count = count($arIp);
		for ($i = 0; $i < $count; $i++)
		{
			$new_ip = trim($arIp[$i]);

			$new_ip = filter_var($new_ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4 | FILTER_FLAG_IPV6);
			if ($new_ip === false)
				$new_ip = '';

			$updates['ip' . $i] = $arIp[$i];
			$new_ips['newip' . $i] = $new_ip;
			$cases[$arIp[$i]] = 'WHEN ' . $oldCol . ' = {string:ip' . $i . '} THEN {inet:newip' . $i . '}';

			// Execute updates every $setSize & also when done with contents of $arIp
			if ((($i + 1) == $count) || (($i + 1) % $setSize === 0))
			{
				$updates['whereSet'] = array_values($updates);
				$smcFunc['db_query']('', '
					UPDATE {db_prefix}' . $targetTable . '
					SET ' . $newCol . ' = CASE ' .
					implode('
						', $cases) . '
						ELSE NULL
					END
					WHERE ' . $oldCol . ' IN ({array_string:whereSet})',
					array_merge($updates, $new_ips)
				);

				$updates = array();
				$new_ips = array();
				$cases = array();
			}
		}

		$_GET['a'] += $limit;
		$step_progress['current'] = $_GET['a'];
	}

	$step_progress = array();
	unset($_GET['a']);
	unset($_GET['total_fixes']);
}
?>