By Matt B, 2016-10-17: To help users with 101+ Facebook import schedules that have migrated from the legacy Facebook Importer plugin to the Event Aggregator service. When the schedules are imported, they come in as "On Demand". This MySQL stored procedure alters the schedules to "weekly" rather than "on_demand" and separates the schedules out in…

tribe-update-ea-schedule.sql

DELIMITER $$
DROP PROCEDURE IF EXISTS tribe_update_ea_schedule$$
CREATE PROCEDURE tribe_update_ea_schedule()
BEGIN
	/**
	 *
	 * This procedure updates all on_demand Facebook schedules so they execute weekly. If there are more than 100,
	 * the schedules are spread out over multiple days.
	 *
	 */
	DECLARE int_days INT;
	DECLARE str_frequency VARCHAR(10);
	DECLARE int_batch INT;
	
	/* Batch size of records to update. Set the batch size to a MAXIMUM of 100 records */
	SET int_batch = 100;
	
	/* Current day counter */
	SET int_days = 1;
	
	/* Frequency you'd like to change the On-Demand import frequency to */
	SET str_frequency = 'weekly';
	
	WHILE( int_days < 7 ) DO
		/* First, we are going to update the post_modified_gmt field based on the number of times through the loop we've travelled */
		/* We are leveraging the menu_order field to track the posts we've updated. Don't worry, it'll be set back to 0 */
		UPDATE
			wp_posts
		SET
			post_modified_gmt = DATE( DATE_ADD( CURDATE(), INTERVAL int_days DAY ) ),
			menu_order = 1
		WHERE
			menu_order = 0
			AND post_parent = 0
			AND EXISTS(
				SELECT
					1
				FROM
					wp_postmeta pm2
				WHERE
					pm2.post_id = wp_posts.ID
					AND pm2.meta_key = '_tribe_aggregator_origin'
					AND pm2.meta_value = 'facebook'
				)
			AND EXISTS(
				SELECT
					1
				FROM
					wp_postmeta pm2
				WHERE
					pm2.post_id = wp_posts.ID
					AND pm2.meta_key = '_tribe_aggregator_type'
					AND pm2.meta_value = 'schedule'
				)
			AND EXISTS(
				SELECT
					1
				FROM
					wp_postmeta pm2
				WHERE
					pm2.post_id = wp_posts.ID
					AND pm2.meta_key = '_tribe_aggregator_frequency'
					AND pm2.meta_value = 'on_demand'
				)
		ORDER BY ID
		LIMIT int_batch;
		
		SET int_days = int_days + 1;
		SELECT int_days;
	END WHILE;

	/* Let's update the on_demand frequency to str_frequency for schedules that were manipulated during the loop above */
	REPLACE INTO wp_postmeta (
		meta_id,
		post_id,
		meta_key,
		meta_value
	) SELECT
		meta_id,
		post_id,
		meta_key,
		str_frequency
	FROM
		wp_postmeta pm1
	WHERE
		pm1.meta_key = '_tribe_aggregator_frequency'
		AND pm1.meta_value = 'on_demand'
		AND EXISTS(
			SELECT
				1
			FROM
				wp_posts
			WHERE
				wp_posts.ID = pm1.post_id
				AND menu_order = 1
		)
		AND EXISTS(
			SELECT
				1
			FROM
				wp_postmeta pm2
			WHERE
				pm2.post_id = pm1.post_id
				AND pm2.meta_key = '_tribe_aggregator_origin'
				AND pm2.meta_value = 'facebook'
			)
		AND EXISTS(
			SELECT
				1
			FROM
				wp_postmeta pm2
			WHERE
				pm2.post_id = pm1.post_id
				AND pm2.meta_key = '_tribe_aggregator_type'
				AND pm2.meta_value = 'schedule'
			);

	/* Set the post's menu order back to 0 and update the post_content with the new frequency so it renders nicely in the UI */
	UPDATE
		wp_posts
	SET
		menu_order = 0,
		post_content = str_frequency
	WHERE
		menu_order = 1
		AND post_parent = 0
		AND post_content = 'on_demand'
		AND EXISTS(
			SELECT
				1
			FROM
				wp_postmeta pm2
			WHERE
				pm2.post_id = wp_posts.ID
				AND pm2.meta_key = '_tribe_aggregator_origin'
				AND pm2.meta_value = 'facebook'
			)
		AND EXISTS(
			SELECT
				1
			FROM
				wp_postmeta pm2
			WHERE
				pm2.post_id = wp_posts.ID
				AND pm2.meta_key = '_tribe_aggregator_type'
				AND pm2.meta_value = 'schedule'
			)
		AND EXISTS(
			SELECT
				1
			FROM
				wp_postmeta pm2
			WHERE
				pm2.post_id = wp_posts.ID
				AND pm2.meta_key = '_tribe_aggregator_frequency'
				AND pm2.meta_value = str_frequency
			);
	
END$$
DELIMITER ;

CALL tribe_update_ea_schedule();