-----Date: 22-jan-2025----- -----Queries are listed below---- -- Step 1: Add a column `is_announcement` to the `user_post` table ALTER TABLE user_post ADD COLUMN is_announcement BOOLEAN NOT NULL DEFAULT FALSE; -- Step 2: Migrate posts from the `announcements` table to the `user_post` table -- Insert data from announcements into user_post, setting is_announcement as TRUE INSERT INTO user_post (text, user_id, created_at, updated_at, is_announcement) SELECT caption, user_id, created_at, updated_at, TRUE FROM announcements; -- Step 3: Fetch the mapping of old announcement IDs to new user_post IDs -- This assumes the `id` column in both tables is the primary key SELECT a.id AS old_announcement_id, u.id AS new_user_post_id FROM announcement a JOIN user_post u ON a.caption = u.text AND a.user_id = u.user_id AND u.is_announcement = TRUE -- Step 4: Update the `user_post_images` table with the new `user_post_id` UPDATE user_post_images JOIN ( SELECT a.id AS old_announcement_id, u.id AS new_user_post_id FROM announcement a JOIN user_post u ON a.caption = u.text AND a.user_id = u.user_id AND u.is_announcement = TRUE ) AS mp ON user_post_images.user_post_id = mp.old_announcement_id SET user_post_images.user_post_id = mp.new_user_post_id; -- (Optional) Step 5: Clean up the announcements table if no longer needed -- DELETE FROM announcements; -- Step 6: Ensure `is_announcement` is FALSE for all existing posts in `user_post` UPDATE user_post SET is_announcement = FALSE WHERE is_announcement IS NULL; ----Step 7: for updating the data related to mandir_id , status , image, deleted_at ----- UPDATE user_post up JOIN announcements a ON up.text = a.caption -- Adjust this condition to match your mapping logic AND up.created_at = a.created_at -- Optional additional matching criteria SET up.mandir_id = a.mandir_id, up.status = a.status, up.image_id = a.image, up.deleted_at = a.deleted_at WHERE up.is_announcement = TRUE; -- Step 8: Add a new column 'announcement_id' to the 'user_post' table ALTER TABLE user_post ADD COLUMN announcement_id INT; -- Step 9: Update the 'announcement_id' column with the corresponding 'id' from the 'announcements' table UPDATE user_post up JOIN announcement a ON up.text = a.caption -- Adjust this condition to match your mapping logic AND up.created_at = a.created_at -- Optional additional matching criteria SET up.announcement_id = a.id WHERE up.is_announcement = TRUE; ---Step 10 :Update the user_post_images table by setting the user_post_id ---- -- based on the announcement_id from the user_post table --- UPDATE gurujikaashramapp_api_app.user_post_images upi JOIN gurujikaashramapp_api_app.user_post up ON upi.user_announcement_id = up.announcement_id AND upi.mandir_id = up.mandir_id AND upi.name = up.text SET upi.user_post_id = up.announcement_id WHERE up.is_announcement = TRUE AND upi.user_post_id IS NULL; ---Step 11: verify the result after updating ------ SELECT upi.user_post_id, up.id AS new_user_post_id, up.announcement_id FROM gurujikaashramapp_api_app.user_post_images upi JOIN gurujikaashramapp_api_app.user_post up ON upi.user_announcement_id = up.announcement_id AND upi.mandir_id = up.mandir_id AND upi.name = up.text WHERE up.is_announcement = TRUE; ---------------------------------------------------------------- Date:24-jan-2025 Queries are listed below NOTE:This script is used for Playlist changes only ----Step 1 : To add Like column in the Playlist Table---- ALTER TABLE playlist ADD COLUMN `liked` BOOLEAN NOT NULL DEFAULT FALSE; -----Step 2:Update play_count to its intial state comes from song_counter table --- SET SQL_SAFE_UPDATES = 0; UPDATE playlist p JOIN ( SELECT user_song_id, COUNT(*) AS counter FROM song_counters WHERE user_song_id IS NOT NULL GROUP BY user_song_id ) sc ON p.id = sc.user_song_id SET p.play_count = sc.counter WHERE p.id = sc.user_song_id; SET SQL_SAFE_UPDATES = 1; -----Step 3:Update Like column to its intial state from Liked table (please ignore this)----- SET SQL_SAFE_UPDATES = 0; UPDATE playlist p JOIN likes l ON p.id = l.user_song_id AND p.user_id=l.user_id SET p.liked = 1 WHERE l.user_song_id IS NOT NULL; SET SQL_SAFE_UPDATES = 1; ---Step 4 : to verify result after Like update (please ignore this)--- select * from playlist p JOIN likes l ON p.id = l.user_song_id AND p.user_id=l.user_id WHERE l.user_song_id IS NOT NULL and p.liked=1; ----------------------new updation as per instruction giving by sir-------------------- -----Step 1: Drop liked column from playlist----- ALTER TABLE playlist DROP COLUMN `liked`; -----Step 2: Add new column liked_count--- ALTER TABLE playlist ADD COLUMN `liked_count` INT NOT NULL DEFAULT 0; -----Step 3:update count in the liked_count ---- SET SQL_SAFE_UPDATES = 0; UPDATE playlist p SET liked_count = ( SELECT COUNT(*) FROM likes l WHERE l.user_song_id = p.id AND l.user_song_id IS NOT NULL ); SET SQL_SAFE_UPDATES = 1; -----Step 4: verify results after update query--- SELECT * FROM likes l join playlist p on p.id=l.user_song_id WHERE l.user_song_id = p.id AND l.user_song_id IS NOT NULL; ------------------------------------------------------------------------------------------------------------------------------ Date:27-jan-2025 Queries are listed below ALTER TABLE `user_post_images` MODIFY `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; ALTER TABLE deviceversion MODIFY COLUMN version DOUBLE(8,2); ---------------------------------------------------------------- Date:29-jan-2025 Queries are listed below ----Step 1 : To add Liked and playCount column in the satsang_sharings Table---- ALTER TABLE satsang_sharings ADD COLUMN `liked` INT NOT NULL DEFAULT 0; ALTER TABLE satsang_sharings ADD COLUMN `play_count` INT NOT NULL DEFAULT 0; -----Step 2:Update play_count to its intial state comes from satsang_play table --- SET SQL_SAFE_UPDATES = 0; UPDATE satsang_sharings p JOIN ( SELECT satsang_id, COUNT(*) AS counter FROM satsang_play WHERE satsang_id IS NOT NULL GROUP BY satsang_id ) sc ON p.id = sc.satsang_id SET p.play_count = sc.counter WHERE p.id = sc.satsang_id; SET SQL_SAFE_UPDATES = 1; -----Step 3:Update liked to its intial state comes from satsang_likes table --- SET SQL_SAFE_UPDATES = 0; UPDATE satsang_sharings p SET liked = ( SELECT COUNT(*) FROM satsang_likes l WHERE l.satsang_id = p.id AND l.satsang_id IS NOT NULL ); SET SQL_SAFE_UPDATES = 1; -----Step 3:verify liked after updating intial state comes from satsang_likes table --- SELECT * FROM satsang_likes l join satsang_sharings p on p.id=l.satsang_id WHERE l.satsang_id = p.id AND l.satsang_id IS NOT NULL; -------------------------------------------------------------------------- Date: 30-jan-2025 Queries are listed below ----Step 1 : To add Liked column in the user_post Table---- ALTER TABLE user_post ADD COLUMN `liked` INT NOT NULL DEFAULT 0; -----Step 2:Update liked to its intial state comes from user_post table --- SET SQL_SAFE_UPDATES = 0; UPDATE user_post p SET liked = ( SELECT COUNT(*) FROM likes l WHERE l.user_post_id = p.id AND l.user_post_id IS NOT NULL ); SET SQL_SAFE_UPDATES = 1; -------------------------------------------------------------------------------------------- Date:2-feb-2024 Queries: ------------------ updated fcm_token in the users table as per instructions giving by charan sir (ignore this) -------------------------------- update users set fcm_token=null where id in (41,117,1234,17546,17596); update users set fcm_token='fwn6EJ-XS8GCPa3p1Yvlho:APA91bFE3XNupX6xBlzL4iRCJa9POJRn0fbHJHumDUsaNd8sHlhwzMRGgpYiSHs94Y_2I6TTFkg7pXXH55BAoKRNpZ1bnpgZSXme3FlgNtBonYxVJ7-CcHs' where id in (41,117,1234,17546,17596); ------------------------------------------------------------------------------------------------------------------ Date:04-feb-2025 Queries: -------------------------------------update column default 0 value ------------------ ALTER TABLE user_post MODIFY COLUMN is_announcement TINYINT DEFAULT 0; ------------------------------------------------------------------------------------------------------------------ Date:05-feb-2025 Queries: -------------------------------------update youtube_broadcast last_heartbeat_at column ------------------ ALTER TABLE youtube_broadcast ADD COLUMN last_heartbeat_at DATETIME NULL AFTER status; ------------------------------------------------------------------------------------------------------ Date:06-feb-2025 Queries: -------------------------------------update youtube_broadcast youtube_token column------------------------------------ ALTER TABLE youtube_broadcast ADD COLUMN youtube_token LONGTEXT NULL AFTER last_heartbeat_at; ----------------------------------------------------------------------------------------------------------------- Date:07-feb-2025 Queries: -----------------------------------------------Create Splash screen table-------------------------------------------------------- CREATE TABLE `splash_screen` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci -------------------------------------------------------------------------------------------------------------------- Date:07-feb-2025 Queries: ----------------------------------updated user_post_video table with video_thumbnail column ------------------------ ALTER TABLE user_post_video ADD COLUMN video_thumbnail VARCHAR(255) NULL AFTER video; -------------------------------------------------------------------------------------------------------------------- Date:07-feb-2025 Queries: ----------------------------------updated user_post_video table with video_thumbnail column ------------------------ ALTER TABLE user_post_video ADD COLUMN video_thumbnail VARCHAR(255) NULL AFTER video; ---------------------------------------------------------------------------------------------------------------- Date:11-feb-2025 Queries: ----------------------------------updated users table with device_id column ------------------------ ALTER TABLE users ADD COLUMN device_id TEXT NULL AFTER last_login; ---------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- Date:12-feb-2025 Queries: ---------------------------------updated notification table for user_post_id -------------------------------- ALTER TABLE notifications ADD COLUMN `user_post_id` INT NULL; ---------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------- Date:11-feb-2025 Queries: ----------------------------------updated users table with device_id column ------------------------ ALTER TABLE users ADD COLUMN device_id TEXT NULL AFTER last_login; ---------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------- Date:21-feb-2024 Queries: -------------------------------------upodated admin notification table for columns--------------------------------------- ALTER TABLE admin_notifications ADD related_to VARCHAR(255) NOT NULL, ADD mandir_id INT NULL, ADD when_to_send ENUM('NOW', 'LATER') NOT NULL, ADD send_time TIMESTAMP NULL; -------------------------------------------------------------------------------------------------------------------