refactor: replace myth/auth with codeigniter/shield

Update instructions

DELIMITER $$

CREATE OR REPLACE PROCEDURE is_foreign_key_disabled()
BEGIN
  DECLARE fkc INT;
  SELECT @@foreign_key_checks INTO fkc;
  IF (fkc = 1) THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Please disable foreign key checks and try again.';
  END IF;
END$$

DELIMITER ;

-- --------------------------------------------------------
-- Remove foreign key checks for created_by and updated_by columns
-- This doesn't work in phpmyadmin where user has to disable it in the query form
-- --------------------------------------------------------

SET foreign_key_checks = 0;

-- --------------------------------------------------------
-- First check that foreign_key_checks are disabled
-- --------------------------------------------------------

CALL is_foreign_key_disabled();

DROP PROCEDURE IF EXISTS is_foreign_key_disabled;

-- --------------------------------------------------------
-- No foreign key checks, all good. Let's begin the auth migration
-- --------------------------------------------------------

START TRANSACTION;

-- --------------------------------------------------------
-- save users details in a temporary users table
-- --------------------------------------------------------

CREATE TEMPORARY TABLE IF NOT EXISTS `cp_users_tmp` AS (SELECT * FROM `cp_users`);
CREATE TEMPORARY TABLE IF NOT EXISTS `cp_podcasts_users_tmp` AS (SELECT * FROM `cp_podcasts_users`);

-- --------------------------------------------------------
-- delete all myth-auth tables
-- --------------------------------------------------------

DROP TABLE IF EXISTS
    `cp_auth_activation_attempts`,
    `cp_auth_groups`,
    `cp_auth_groups_permissions`,
    `cp_auth_groups_users`,
    `cp_auth_logins`,
    `cp_auth_permissions`,
    `cp_auth_reset_attempts`,
    `cp_auth_tokens`,
    `cp_auth_users_permissions`,
    `cp_podcasts_users`,
    `cp_users`;

-- --------------------------------------------------------
-- insert all shield tables
-- --------------------------------------------------------

CREATE TABLE `cp_auth_groups_users` (
  `id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `group` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cp_auth_identities` (
  `id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `secret` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `secret2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `expires` datetime DEFAULT NULL,
  `extra` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `force_reset` tinyint(1) NOT NULL DEFAULT 0,
  `last_used_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cp_auth_logins` (
  `id` int(11) UNSIGNED NOT NULL,
  `ip_address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `identifier` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int(11) UNSIGNED DEFAULT NULL,
  `date` datetime NOT NULL,
  `success` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cp_auth_permissions_users` (
  `id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `permission` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cp_auth_remember_tokens` (
  `id` int(11) UNSIGNED NOT NULL,
  `selector` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `hashedValidator` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `expires` datetime NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cp_auth_token_logins` (
  `id` int(11) UNSIGNED NOT NULL,
  `ip_address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `identifier` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int(11) UNSIGNED DEFAULT NULL,
  `date` datetime NOT NULL,
  `success` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cp_users` (
  `id` int(11) UNSIGNED NOT NULL PRIMARY KEY,
  `username` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status_message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 0,
  `last_active` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `is_owner` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- insert users + identities + add to appropriate groups
-- --------------------------------------------------------

INSERT INTO `cp_users` (`id`, `username`, `status`, `status_message`, `active`, `last_active`, `created_at`, `updated_at`, `deleted_at`, `is_owner`)
SELECT * FROM (
  SELECT `id`, `username`, `status`, `status_message`, `active`, NULL, `created_at`, `updated_at`, `deleted_at`, 1 as `is_owner`
  FROM `cp_users_tmp`
  WHERE `id` IN (SELECT MIN(`id`) FROM `cp_users_tmp`)
UNION
  SELECT `id`, `username`, `status`, `status_message`, `active`, NULL, `created_at`, `updated_at`, `deleted_at`, 0 as `is_owner`
  FROM `cp_users_tmp`
  WHERE `id` NOT IN (SELECT MIN(`id`) FROM `cp_users_tmp`)
) as U;

INSERT INTO `cp_auth_identities` (`id`, `user_id`, `type`, `name`, `secret`, `secret2`, `expires`, `extra`, `force_reset`, `last_used_at`, `created_at`, `updated_at`)
SELECT `id`, `id`, 'email_password', NULL, `email`, `password_hash`, NULL, NULL, 0, NULL, `created_at`, `updated_at` FROM `cp_users_tmp`;

SET @auth_groups_users_id := 0;

-- set instance owner as super admin and others as podcasters

INSERT INTO `cp_auth_groups_users` (`id`, `user_id`, `group`, `created_at`)
SELECT * FROM (
  SELECT (@auth_groups_users_id := @auth_groups_users_id + 1), `id`, 'superadmin', `created_at`
  FROM `cp_users_tmp`
  WHERE `id` IN (SELECT MIN(`id`) FROM `cp_users_tmp`)
UNION
  SELECT (@auth_groups_users_id := @auth_groups_users_id + 1), `id`, 'podcaster', `created_at`
  FROM `cp_users_tmp`
  WHERE `id` NOT IN (SELECT MIN(`id`) FROM `cp_users_tmp`)
) as U2;

-- set podcast owners as podcast admins + other podcast contributors as guests

INSERT INTO `cp_auth_groups_users` (`id`, `user_id`, `group`, `created_at`)
SELECT * FROM (
  SELECT (@auth_groups_users_id := @auth_groups_users_id + 1), `created_by`, CONCAT('podcast#', `id`, '-admin'), `created_at`
  FROM `cp_podcasts`
UNION
  SELECT (@auth_groups_users_id := @auth_groups_users_id + 1), `cp_podcasts_users_tmp`.`user_id`, CONCAT('podcast#', `id`, '-guest'), `cp_podcasts`.`created_at`
  FROM `cp_podcasts_users_tmp` JOIN `cp_podcasts` ON `cp_podcasts_users_tmp`.`podcast_id` = `cp_podcasts`.`id`
  WHERE `cp_podcasts`.`created_by` != `cp_podcasts_users_tmp`.`user_id`
) as U3;


-- --------------------------------------------------------
-- Add Indexes
-- --------------------------------------------------------

ALTER TABLE `cp_auth_groups_users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `cp_auth_groups_users_user_id_foreign` (`user_id`);

ALTER TABLE `cp_auth_identities`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `type_secret` (`type`,`secret`),
  ADD KEY `user_id` (`user_id`);

ALTER TABLE `cp_auth_logins`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id_type_identifier` (`id_type`,`identifier`),
  ADD KEY `user_id` (`user_id`);

ALTER TABLE `cp_auth_permissions_users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `cp_auth_permissions_users_user_id_foreign` (`user_id`);

ALTER TABLE `cp_auth_remember_tokens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `selector` (`selector`),
  ADD KEY `cp_auth_remember_tokens_user_id_foreign` (`user_id`);

ALTER TABLE `cp_auth_token_logins`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id_type_identifier` (`id_type`,`identifier`),
  ADD KEY `user_id` (`user_id`);

ALTER TABLE `cp_users`
  ADD UNIQUE KEY `username` (`username`);

-- --------------------------------------------------------
-- Add AUTO_INCREMENT
-- --------------------------------------------------------

ALTER TABLE `cp_auth_groups_users`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `cp_auth_identities`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `cp_auth_logins`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

ALTER TABLE `cp_auth_permissions_users`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `cp_auth_remember_tokens`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `cp_auth_token_logins`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `cp_users`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

-- --------------------------------------------------------
-- Add Constraints
-- --------------------------------------------------------

ALTER TABLE `cp_auth_groups_users`
  ADD CONSTRAINT `cp_auth_groups_users_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `cp_users` (`id`) ON DELETE CASCADE;

ALTER TABLE `cp_auth_identities`
  ADD CONSTRAINT `cp_auth_identities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `cp_users` (`id`) ON DELETE CASCADE;

ALTER TABLE `cp_auth_permissions_users`
  ADD CONSTRAINT `cp_auth_permissions_users_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `cp_users` (`id`) ON DELETE CASCADE;

ALTER TABLE `cp_auth_remember_tokens`
  ADD CONSTRAINT `cp_auth_remember_tokens_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `cp_users` (`id`) ON DELETE CASCADE;

-- --------------------------------------------------------
-- drop temporary table
-- --------------------------------------------------------

DROP TEMPORARY TABLE `cp_users_tmp`;
DROP TEMPORARY TABLE `cp_podcasts_users_tmp`;

-- --------------------------------------------------------
-- Reinstate foreign key checks
-- --------------------------------------------------------

SET foreign_key_checks = 1;


COMMIT;