public function run2() { if ($this->hasTableIndex('images', 'id')) { $this->db->Execute('ALTER TABLE images DROP KEY id, ADD UNIQUE KEY `idx_id` (`env_id`,`id`,`platform`,`cloud_location`)'); } $this->db->Execute('ALTER TABLE images ADD `hash` binary(16) NOT NULL FIRST'); foreach ($this->db->GetAll('SELECT * FROM images') as $im) { if ($im['env_id']) { $this->db->Execute('UPDATE images SET hash = UNHEX(?) WHERE env_id = ? AND id = ? AND platform = ? AND cloud_location = ?', [str_replace('-', '', Image::calculateHash($im['env_id'], $im['id'], $im['platform'], $im['cloud_location'])), $im['env_id'], $im['id'], $im['platform'], $im['cloud_location']]); } else { $this->db->Execute('UPDATE images SET hash = UNHEX(?) WHERE ISNULL(env_id) AND id = ? AND platform = ? AND cloud_location = ?', [str_replace('-', '', Image::calculateHash($im['env_id'], $im['id'], $im['platform'], $im['cloud_location'])), $im['id'], $im['platform'], $im['cloud_location']]); } } // remove possible duplicates foreach ($this->db->GetAll('SELECT `hash`, count(*) as cnt FROM images GROUP BY hash HAVING cnt > 1') as $im) { $this->db->Execute('DELETE FROM images WHERE hash = ? LIMIT ?', [$im['hash'], $im['cnt'] - 1]); } $this->db->Execute('ALTER TABLE images ADD PRIMARY KEY(`hash`)'); }