public function getMigrations(Updater $updater)
{
$action = Common::prefixTable('log_action');
$duplicates = Common::prefixTable('log_action_duplicates');
$visitAction = Common::prefixTable('log_link_visit_action');
$conversion = Common::prefixTable('log_conversion');
$visit = Common::prefixTable('log_visit');
return array($this->migration->db->addColumn('log_action', 'url_prefix', 'TINYINT(2) NULL', 'type'), $this->migration->db->sql(" UPDATE `{$action}`\n SET\n url_prefix = IF (\n LEFT(name, 11) = 'http://www.', 1, IF (\n LEFT(name, 7) = 'http://', 0, IF (\n LEFT(name, 12) = 'https://www.', 3, IF (\n LEFT(name, 8) = 'https://', 2, NULL\n )\n )\n )\n ),\n name = IF (\n url_prefix = 0, SUBSTRING(name, 8), IF (\n url_prefix = 1, SUBSTRING(name, 12), IF (\n url_prefix = 2, SUBSTRING(name, 9), IF (\n url_prefix = 3, SUBSTRING(name, 13), name\n )\n )\n )\n ),\n hash = CRC32(name)\n WHERE\n type = 1 AND\n url_prefix IS NULL;\n "), $this->migration->db->dropTable('log_action_duplicates'), $this->migration->db->createTable('log_action_duplicates', array('before' => 'int(10) unsigned NOT NULL', 'after' => 'int(10) unsigned NOT NULL')), $this->migration->db->sql("ALTER TABLE {$duplicates} ADD KEY `mainkey` (`before`)"), $this->migration->db->sql(" INSERT INTO `{$duplicates}` (\n SELECT\n action.idaction AS `before`,\n canonical.idaction AS `after`\n FROM\n (\n SELECT\n name,\n hash,\n MIN(idaction) AS idaction\n FROM\n `{$action}` AS action_canonical_base\n WHERE\n type = 1 AND\n url_prefix IS NOT NULL\n GROUP BY name, hash\n HAVING COUNT(idaction) > 1\n )\n AS canonical\n LEFT JOIN\n `{$action}` AS action\n ON (action.type = 1 AND canonical.hash = action.hash)\n AND canonical.name = action.name\n AND canonical.idaction != action.idaction\n );\n "), $this->migration->db->sql(" UPDATE\n `{$visitAction}` AS link\n LEFT JOIN\n `{$duplicates}` AS duplicates_idaction_url\n ON link.idaction_url = duplicates_idaction_url.before\n SET\n link.idaction_url = duplicates_idaction_url.after\n WHERE\n duplicates_idaction_url.after IS NOT NULL;\n "), $this->migration->db->sql(" UPDATE\n `{$visitAction}` AS link\n LEFT JOIN\n `{$duplicates}` AS duplicates_idaction_url_ref\n ON link.idaction_url_ref = duplicates_idaction_url_ref.before\n SET\n link.idaction_url_ref = duplicates_idaction_url_ref.after\n WHERE\n duplicates_idaction_url_ref.after IS NOT NULL;\n "), $this->migration->db->sql(" UPDATE\n `{$conversion}` AS conversion\n LEFT JOIN\n `{$duplicates}` AS duplicates\n ON conversion.idaction_url = duplicates.before\n SET\n conversion.idaction_url = duplicates.after\n WHERE\n duplicates.after IS NOT NULL;\n "), $this->migration->db->sql(" UPDATE\n `{$visit}` AS visit\n LEFT JOIN\n `{$duplicates}` AS duplicates_entry\n ON visit.visit_entry_idaction_url = duplicates_entry.before\n SET\n visit.visit_entry_idaction_url = duplicates_entry.after\n WHERE\n duplicates_entry.after IS NOT NULL;\n "), $this->migration->db->sql(" UPDATE\n `{$visit}` AS visit\n LEFT JOIN\n `{$duplicates}` AS duplicates_exit\n ON visit.visit_exit_idaction_url = duplicates_exit.before\n SET\n visit.visit_exit_idaction_url = duplicates_exit.after\n WHERE\n duplicates_exit.after IS NOT NULL;\n "), $this->migration->db->sql(" DELETE action FROM\n `{$action}` AS action\n LEFT JOIN\n `{$duplicates}` AS duplicates\n ON action.idaction = duplicates.before\n WHERE\n duplicates.after IS NOT NULL;\n "), $this->migration->db->dropTable('log_action_duplicates'));
}