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;
}
