File indexing completed on 2024-03-24 06:03:44
0001 INSERT INTO `pling-import`.`activity_log_types` (`activity_log_type_id`, `type_text`) 0002 VALUES ('321', 'BackendUserMerged'); 0003 0004 0005 CREATE TABLE `merge_member_log` 0006 ( 0007 `id` INT NOT NULL AUTO_INCREMENT, 0008 `object_type` VARCHAR(50) NOT NULL COMMENT 'project, comment, rating,...', 0009 `object_id` INT NOT NULL, 0010 `member_id_org` INT NOT NULL COMMENT 'MemberId vor dem Merge', 0011 `member_id_new` INT NOT NULL COMMENT 'MemberId nach dem Merge', 0012 PRIMARY KEY (`id`) 0013 ) 0014 COLLATE = 'latin1_swedish_ci' 0015 ; 0016 0017 0018 0019 0020 0021 DROP PROCEDURE `merge_members`; 0022 0023 0024 DELIMITER // 0025 CREATE PROCEDURE `merge_members`(IN `from_member_id` INT, 0026 IN `to_member_id` INT) 0027 COMMENT 'Merge of 2 members into 1' 0028 BEGIN 0029 0030 DECLARE EXIT HANDLER FOR SQLEXCEPTION 0031 BEGIN 0032 -- ERROR 0033 ROLLBACK; 0034 END; 0035 0036 START TRANSACTION; 0037 0038 #Update table member 0039 UPDATE `member` `m` 0040 SET `m`.`is_active` = 0 0041 , `m`.`is_deleted` = 1 0042 , `m`.`deleted_at` = NOW() 0043 WHERE `m`.`member_id` = `from_member_id`; 0044 0045 #Update table member_email 0046 UPDATE `member_email` `me` 0047 SET `me`.`email_deleted` = 1 0048 WHERE `me`.`email_member_id` = `from_member_id`; 0049 0050 #Update table project 0051 INSERT INTO `merge_member_log` 0052 ( 0053 SELECT NULL, 'project', `project_id`, `member_id`, `to_member_id` 0054 FROM `project` `p` 0055 WHERE `p`.`member_id` = `from_member_id` 0056 AND `p`.`type_id` = 1 0057 ); 0058 0059 UPDATE `project` `p` 0060 SET `p`.`member_id` = `to_member_id` 0061 WHERE `p`.`member_id` = `from_member_id` 0062 AND `p`.`type_id` = 1; 0063 0064 #Update table comments 0065 INSERT INTO `merge_member_log` 0066 ( 0067 SELECT NULL, 'comments', `comment_id`, `comment_member_id`, `to_member_id` 0068 FROM `comments` `c` 0069 WHERE `c`.`comment_member_id` = `from_member_id` 0070 ); 0071 0072 UPDATE `comments` `c` 0073 SET `c`.`comment_member_id` = `to_member_id` 0074 WHERE `c`.`comment_member_id` = `from_member_id`; 0075 0076 #Update table project_follower 0077 INSERT INTO `merge_member_log` 0078 ( 0079 SELECT NULL, 'project_follower', `project_follower_id`, `member_id`, `to_member_id` 0080 FROM `project_follower` `f` 0081 WHERE `f`.`member_id` = `from_member_id` 0082 ); 0083 0084 UPDATE `project_follower` `f` 0085 SET `f`.`member_id` = `to_member_id` 0086 WHERE `f`.`member_id` = `from_member_id`; 0087 0088 #Update table project_rating 0089 INSERT INTO `merge_member_log` 0090 ( 0091 SELECT NULL, 'project_rating', `rating_id`, `member_id`, `to_member_id` 0092 FROM `project_rating` `r` 0093 WHERE `r`.`member_id` = `from_member_id` 0094 ); 0095 0096 UPDATE `project_rating` `r` 0097 SET `r`.`member_id` = `to_member_id` 0098 WHERE `r`.`member_id` = `from_member_id`; 0099 0100 #Update table project_plings 0101 INSERT INTO `merge_member_log` 0102 ( 0103 SELECT NULL, 'project_plings', `project_plings_id`, `member_id`, `to_member_id` 0104 FROM `project_plings` `r` 0105 WHERE `r`.`member_id` = `from_member_id` 0106 ); 0107 0108 UPDATE `project_plings` `r` 0109 SET `r`.`member_id` = `to_member_id` 0110 WHERE `r`.`member_id` = `from_member_id`; 0111 0112 0113 #Update ppload 0114 0115 IF (SELECT count(1) FROM `ppload`.`ppload_collections` `pc` WHERE `pc`.`owner_id` = `from_member_id`) > 0 0116 THEN 0117 0118 #Update ppload_collections 0119 INSERT INTO `merge_member_log` 0120 ( 0121 SELECT NULL, 'ppload_collections', `pc`.`id`, `pc`.`owner_id`, `to_member_id` 0122 FROM `ppload`.`ppload_collections` `pc` 0123 WHERE `pc`.`owner_id` = `from_member_id` 0124 ); 0125 0126 UPDATE `ppload`.`ppload_collections` `pc` 0127 SET `pc`.`owner_id` = `to_member_id` 0128 WHERE `pc`.`owner_id` = `from_member_id`; 0129 0130 #Update ppload_files 0131 INSERT INTO `merge_member_log` 0132 ( 0133 SELECT NULL, 'ppload_files', `pc`.`id`, `pc`.`owner_id`, `to_member_id` 0134 FROM `ppload`.`ppload_files` `pc` 0135 WHERE `pc`.`owner_id` = `from_member_id` 0136 ); 0137 0138 UPDATE `ppload`.`ppload_files` `pf` 0139 SET `pf`.`owner_id` = `to_member_id` 0140 WHERE `pf`.`owner_id` = `from_member_id`; 0141 0142 #Update ppload_files_downloaded? 0143 /*INSERT INTO merge_member_log 0144 ( 0145 SELECT null, 'ppload_files_downloaded', pc.id, pc.owner_id, to_member_id 0146 FROM ppload.ppload_files_downloaded pc WHERE pc.owner_id = from_member_id 0147 );*/ 0148 0149 UPDATE `ppload`.`ppload_files_downloaded` `pfd` 0150 SET `pfd`.`owner_id` = `to_member_id` 0151 WHERE `pfd`.`owner_id` = `from_member_id`; 0152 0153 /** 0154 #Update ppload_profiles 0155 INSERT INTO merge_member_log 0156 ( 0157 SELECT null, 'ppload_profiles', pc.id, pc.owner_id, to_member_id 0158 FROM ppload_profiles pc WHERE pc.owner_id = from_member_id 0159 ); 0160 0161 UPDATE ppload.ppload_profiles pp 0162 SET pp.owner_id = to_member_id 0163 WHERE pp.owner_id = from_member_id; 0164 **/ 0165 0166 END IF; 0167 0168 0169 #Write a log entry 0170 INSERT INTO `activity_log` (`member_id`, `object_id`, `object_ref`, `object_title`, `object_text`, 0171 `activity_type_id`, `time`) 0172 VALUES ('22', `from_member_id`, 'member', 'call merge_members', 0173 CONCAT('merge member ', `from_member_id`, ' into member ', `to_member_id`), '321', NOW()); 0174 COMMIT; 0175 0176 END // 0177 DELIMITER ; 0178 0179 0180 0181 0182