function fix_unique_index($tableid) { global $vbphrase, $db, $uniquetables; if (isset($uniquetables["{$tableid}"])) { $table =& $uniquetables["{$tableid}"]; } else { return -1; } $unique_keys = array(); $keys = array(); $checkindexes = $db->query_write("SHOW KEYS FROM " . TABLE_PREFIX . "{$table['name']}"); while ($checkindex = $db->fetch_array($checkindexes)) { if ($checkindex['Non_unique'] == 0) { $unique_keys["{$checkindex['Key_name']}"][] = $checkindex['Column_name']; } $keys["{$checkindex['Key_name']}"][] = $checkindex['Column_name']; } $db->free_result($checkindexes); foreach ($unique_keys as $keyname => $keyfields) { $unique_keys["{$keyname}"] = implode(', ', $keyfields); } $fields = implode(', ', $table['fields']); $gotunique = in_array($fields, $unique_keys); if ($gotunique) { echo "<div>" . construct_phrase($vbphrase['table_x_has_unique_index'], "<strong>{$table['name']}</strong>") . "</div>"; $nexttableid = fix_unique_index($tableid + 1); } else { echo "<p>" . construct_phrase($vbphrase['replacing_unique_index_on_table_x'], "<strong>{$table['name']}</strong>") . "</p><ul>"; $fields = implode(', ', $table['fields']); $findquery = "SELECT {$fields}, COUNT(*) AS occurences\n\t\t\t\tFROM " . TABLE_PREFIX . "{$table['name']}\n\t\t\t\tGROUP BY {$fields}\n\t\t\t\tHAVING occurences > 1"; $dupes = $db->query_write($findquery); if ($numdupes = $db->num_rows($dupes)) { echo "<li>" . construct_phrase($vbphrase['found_x_duplicate_record_occurences'], "<strong>{$numdupes}</strong>") . "<ol>"; while ($dupe = $db->fetch_array($dupes)) { $cond = array(); foreach ($dupe as $fieldname => $field) { if ($fieldname != 'occurences') { $cond[] = "{$fieldname} = " . iif(is_numeric($field), $field, "'" . $db->escape_string($field) . "'"); } } $dupesquery = "DELETE FROM " . TABLE_PREFIX . "{$table['name']} WHERE " . implode(" AND ", $cond) . " "; if ($table['autoinc']) { $max = $db->query_first("\n\t\t\t\t\t\t\tSELECT MAX({$table['autoinc']}) AS maxid\n\t\t\t\t\t\t\tFROM " . TABLE_PREFIX . "{$table['name']}\n\t\t\t\t\t\t\tWHERE " . implode("\nAND ", $cond) . "\n\t\t\t\t\t\t"); $dupesquery .= "AND {$table['autoinc']} <> {$max['maxid']}"; } else { $dupesquery .= "LIMIT " . ($dupe['occurences'] - 1); } $db->query_write($dupesquery); echo "<li>{$vbphrase['deleted_duplicate_occurence']}</li>"; } $db->free_result($dupes); echo "</ol></li>"; } if (isset($keys["{$table['keyname']}"])) { $killindexquery = "ALTER TABLE " . TABLE_PREFIX . "{$table['name']} DROP INDEX {$table['keyname']}"; echo "<li>{$vbphrase['dropping_non_unique_index']} <!--<pre>{$killindexquery}</pre>-->"; $db->query_write($killindexquery); echo "{$vbphrase['done']}</li>"; } $createindexquery = "ALTER TABLE " . TABLE_PREFIX . "{$table['name']} ADD " . iif($table['name'] == 'access', 'PRIMARY', 'UNIQUE') . " KEY {$table['keyname']} ({$fields})"; echo "<li>{$vbphrase['creating_unique_index']} <!--<pre>{$createindexquery}</pre>-->"; $db->query_write($createindexquery); echo "{$vbphrase['done']}</li>"; echo "</ul>"; $nexttableid = $tableid + 1; } return $nexttableid; }
function fix_unique_index($tableid) { global $vbphrase, $db, $uniquetables; if (isset($uniquetables["$tableid"])) { $table =& $uniquetables["$tableid"]; } else { return -1; } $unique_keys = array(); $keys = array(); $checkindexes = $db->query_write("SHOW KEYS FROM " . TABLE_PREFIX . "$table[name]"); while ($checkindex = $db->fetch_array($checkindexes)) { if ($checkindex['Non_unique'] == 0) { $unique_keys["$checkindex[Key_name]"][] = $checkindex['Column_name']; } $keys["$checkindex[Key_name]"][] = $checkindex['Column_name']; } $db->free_result($checkindexes); foreach ($unique_keys AS $keyname => $keyfields) { $unique_keys["$keyname"] = implode(', ', $keyfields); } $fields = implode(', ', $table['fields']); $gotunique = in_array($fields, $unique_keys); if ($gotunique) { echo "<div>" . construct_phrase($vbphrase['table_x_has_unique_index'], "<strong>$table[name]</strong>") . "</div>"; $nexttableid = fix_unique_index($tableid + 1); } else { echo "<p>" . construct_phrase($vbphrase['replacing_unique_index_on_table_x'], "<strong>$table[name]</strong>") . "</p><ul>"; $fields = implode(', ', $table['fields']); $findquery = "SELECT $fields, COUNT(*) AS occurences FROM " . TABLE_PREFIX . "$table[name] GROUP BY $fields HAVING occurences > 1"; $dupes = $db->query_write($findquery); if ($numdupes = $db->num_rows($dupes)) { echo "<li>" . construct_phrase($vbphrase['found_x_duplicate_record_occurences'], "<strong>$numdupes</strong>") . "<ol>"; while ($dupe = $db->fetch_array($dupes)) { $cond = array(); foreach ($dupe AS $fieldname => $field) { if ($fieldname != 'occurences') { $cond[] = "$fieldname = " . iif(is_numeric($field), $field, "'" . $db->escape_string($field) . "'"); } } $dupesquery = "DELETE FROM " . TABLE_PREFIX . "$table[name] WHERE " . implode(" AND ", $cond) . " "; if ($table['autoinc']) { $max = $db->query_first(" SELECT MAX($table[autoinc]) AS maxid FROM " . TABLE_PREFIX . "$table[name] WHERE " . implode("\nAND ", $cond) . " "); $dupesquery .= "AND $table[autoinc] <> $max[maxid]"; } else { $dupesquery .= "LIMIT " . ($dupe['occurences'] - 1); } $db->query_write($dupesquery); echo "<li>$vbphrase[deleted_duplicate_occurence]</li>"; } $db->free_result($dupes); echo "</ol></li>"; } if (isset($keys["$table[keyname]"])) { $killindexquery = "ALTER TABLE " . TABLE_PREFIX . "$table[name] DROP INDEX $table[keyname]"; echo "<li>$vbphrase[dropping_non_unique_index] <!--<pre>$killindexquery</pre>-->"; $db->query_write($killindexquery); echo "$vbphrase[done]</li>"; } $createindexquery = "ALTER TABLE " . TABLE_PREFIX . "$table[name] ADD " . iif($table['name'] == 'access', 'PRIMARY', 'UNIQUE') . " KEY $table[keyname] ($fields)"; echo "<li>$vbphrase[creating_unique_index] <!--<pre>$createindexquery</pre>-->"; $db->query_write($createindexquery); echo "$vbphrase[done]</li>"; echo "</ul>"; $nexttableid = $tableid + 1; } return $nexttableid; }