File indexing completed on 2024-04-21 06:00:37
0001 START TRANSACTION; 0002 0003 USE pling; 0004 0005 -- set this to the host which fits to the environment 0006 SET @host = 'https://cdn.pling.cc'; -- integration 0007 #SET @host = 'https://cdn.pling.com'; -- live 0008 0009 -- set this to the member table 0010 SET @member_table = 'member'; 0011 #SET @member_table = concat("member_bak_",DATE_FORMAT(NOW(),'%Y%m%d')); -- only for testing purposes 0012 0013 -- name for backup table with current date 0014 SET @backup_table = concat("member_bak_",DATE_FORMAT(NOW(),'%Y%m%d')); 0015 0016 -- backup original member data we going to change 0017 SET @sql = CONCAT("CREATE TABLE ",@backup_table," as select member_id, avatar, avatar_type_id, profile_image_url, profile_image_url_bg, profile_img_src from member"); 0018 PREPARE stmt from @sql; 0019 EXECUTE stmt; 0020 0021 -- update profile_image_url 0022 SET @sql = CONCAT("UPDATE ",@member_table," SET profile_image_url = REPLACE(profile_image_url, 'https://cn.opendesktop.org', '",@host,"') WHERE profile_image_url LIKE 'https://cn.opendesktop.org%'"); 0023 PREPARE stmt from @sql; 0024 EXECUTE stmt; 0025 0026 SET @sql = CONCAT("UPDATE ",@member_table," SET profile_image_url = REPLACE(profile_image_url, 'https://cn.opendesktop.cc', '",@host,"') WHERE profile_image_url LIKE 'https://cn.opendesktop.cc%'"); 0027 PREPARE stmt from @sql; 0028 EXECUTE stmt; 0029 0030 SET @sql = CONCAT("UPDATE ",@member_table," SET profile_image_url = REPLACE(profile_image_url, 'https://cn.pling.com', '",@host,"') WHERE profile_image_url LIKE 'https://cn.pling.com%'"); 0031 PREPARE stmt from @sql; 0032 EXECUTE stmt; 0033 0034 SET @sql = CONCAT("UPDATE ",@member_table," SET profile_image_url = REPLACE(profile_image_url, 'http://cn.pling.com', '",@host,"') WHERE profile_image_url LIKE 'http://cn.pling.com%'"); 0035 PREPARE stmt from @sql; 0036 EXECUTE stmt; 0037 0038 -- update profile_image_url_bg 0039 SET @sql = CONCAT("UPDATE ",@member_table," SET profile_image_url_bg = REPLACE(profile_image_url_bg, 'https://cn.opendesktop.org', '",@host,"') WHERE profile_image_url_bg LIKE 'https://cn.opendesktop.org%'"); 0040 PREPARE stmt from @sql; 0041 EXECUTE stmt; 0042 0043 SET @sql = CONCAT("UPDATE ",@member_table," SET profile_image_url_bg = REPLACE(profile_image_url_bg, 'https://cn.opendesktop.cc', '",@host,"') WHERE profile_image_url_bg LIKE 'https://cn.opendesktop.cc%'"); 0044 PREPARE stmt from @sql; 0045 EXECUTE stmt; 0046 0047 SET @sql = CONCAT("UPDATE ",@member_table," SET profile_image_url_bg = REPLACE(profile_image_url_bg, 'https://cn.pling.com', '",@host,"') WHERE profile_image_url_bg LIKE 'https://cn.pling.com%'"); 0048 PREPARE stmt from @sql; 0049 EXECUTE stmt; 0050 0051 COMMIT;