File indexing completed on 2024-04-21 06:00:35
0001 DROP TABLE IF EXISTS `member_email`; 0002 CREATE TABLE `member_email` 0003 ( 0004 `email_id` int(11) NOT NULL AUTO_INCREMENT, 0005 `email_member_id` int(11) NOT NULL, 0006 `email_address` varchar(255) NOT NULL, 0007 `email_primary` int(1) DEFAULT '0', 0008 `email_deleted` int(1) DEFAULT '0', 0009 `email_created` datetime DEFAULT NULL, 0010 `email_checked` datetime DEFAULT NULL, 0011 `email_verification_value` varchar(255) DEFAULT NULL, 0012 PRIMARY KEY (`email_id`), 0013 KEY `idx_address` (`email_address`), 0014 KEY `idx_member` (`email_member_id`), 0015 KEY `idx_verification` (`email_verification_value`) 0016 ) ENGINE = InnoDB; 0017 0018 DELIMITER $$ 0019 0020 DROP TRIGGER IF EXISTS `member_email_BEFORE_INSERT`$$ 0021 CREATE DEFINER = CURRENT_USER TRIGGER `member_email_BEFORE_INSERT` 0022 BEFORE INSERT 0023 ON `member_email` 0024 FOR EACH ROW 0025 BEGIN 0026 IF `NEW`.`email_created` IS NULL THEN 0027 SET `NEW`.`email_created` = NOW(); 0028 END IF; 0029 END$$ 0030 DELIMITER ; 0031 0032 START TRANSACTION; 0033 0034 -- migrate all user email 0035 TRUNCATE `member_email`; 0036 INSERT INTO `member_email` (`email_member_id`, `email_address`, `email_primary`, `email_created`, `email_checked`, 0037 `email_verification_value`) 0038 SELECT `member`.`member_id` AS `email_member_id`, 0039 `member`.`mail` AS `email_address`, 0040 1 AS `email_primary`, 0041 `member`.`created_at` AS `email_created`, 0042 `member`.`created_at` AS `email_checked`, 0043 `member`.`verificationVal` AS `email_verification_value` 0044 FROM `member` 0045 WHERE `member`.`mail_checked` 0046 AND `member`.`is_active` 0047 AND `member`.`mail` IS NOT NULL 0048 ; 0049 0050 -- backup member table 0051 CREATE TABLE `member_bak_20160928` LIKE `member`; 0052 INSERT `member_bak_20160928` 0053 SELECT * 0054 FROM `member`; 0055 0056 -- after migrating to member_email we can drop the column `validationVal` 0057 ALTER TABLE `member` 0058 DROP COLUMN `verificationVal`; 0059 0060 COMMIT;