Duplicate autosave/revisions clutter the database

[Denis-de-Bernardy]

We shouldn't be storing revisions and autosaves that are identical to the original post.

It generates a bug, to start with. If you open a post, and you leave it alone until the autosave fires, and then close the window, you end up with a notice -- a newer version of the post exists.

Then, it clutters the list of actual revisions with junk that cannot be removed without a plugin or by using SQL.

Mm, actually, the autosave issue described above seems fixed in trunk. But we're still cluttering the database with useless revisions. It's minor, too, so punting pending patch.

I've this function on the save_post hook, in case there is any interest for a patch writer:

	function save_post_revision($rev_id) {
		if ( wp_is_post_autosave($rev_id) ) {
			return;
		} elseif ( $post_id = wp_is_post_revision($rev_id) ) {
			# do nothing
		} else {
			$post_id = $rev_id;
		}
		
		global $wpdb;
		$post = get_post($rev_id);
		
		# drop dup revs
		$kill_ids = $wpdb->get_col("
			SELECT	ID
			FROM	$wpdb->posts
			WHERE	post_type = 'revision'
			AND		ID <> " . intval($rev_id) . "
			AND		post_parent = " . intval($post_id) . "
			AND		post_content = '" . $wpdb->escape($post->post_content) . "'
			");
		
		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
		
		# stop here for real posts
		if ( $post_id == $rev_id )
			return;
		
		# drop other potential dup revs
		$kill_ids = $wpdb->get_col("
			SELECT	p2.ID
			FROM	$wpdb->posts as p2
			JOIN	$wpdb->posts as p1
			ON		p1.post_parent = p2.post_parent
			AND		p1.post_type = p2.post_type
			WHERE	p1.post_type = 'revision'
			AND		p1.post_parent = " . intval($post_id) . "
			AND		p1.post_content = p2.post_content
			AND		p1.ID > p2.ID
			");

		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
		
		# drop near-empty revs
		$kill_ids = $wpdb->get_col("
			SELECT	ID
			FROM	$wpdb->posts
			WHERE	post_type = 'revision'
			AND		post_parent = " . intval($post_id) . "
			AND		LENGTH(post_content) <= 50
			");
		
		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
		
		# drop adjascent revs
		$kill_ids = $wpdb->get_col("
			SELECT	p2.ID
			FROM	$wpdb->posts as p2
			JOIN	$wpdb->posts as p1
			ON		p1.post_parent = p2.post_parent
			AND		p1.post_type = p2.post_type
			WHERE	p1.post_type = 'revision'
			AND		p1.post_parent = " . intval($post_id) . "
			AND		DATEDIFF(p1.post_date, p2.post_date) < 1
			AND		p1.post_date >= p2.post_date
			AND		p1.ID <> p2.ID
			");

		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
		
		# drop near-identical revs
		$kill_ids = $wpdb->get_col("
			SELECT	p2.ID
			FROM	$wpdb->posts as p2
			JOIN	$wpdb->posts as p1
			ON		p1.post_parent = p2.post_parent
			AND		p1.post_type = p2.post_type
			WHERE	p1.post_type = 'revision'
			AND		p1.post_parent = " . intval($post_id) . "
			AND		DATEDIFF(p1.post_date, p2.post_date) <= 7
			AND		ABS( LENGTH(p1.post_content) - LENGTH(p2.post_content) ) <= 50
			AND		p1.post_date >= p2.post_date
			AND		p1.ID <> p2.ID
			");

		if ( $kill_ids ) {
			foreach ( $kill_ids as $kill_id ) {
				wp_delete_post_revision($kill_id);
			}
		}
	} # save_post_revision()

Duplicate of #7392.

similar, but not 100% dup. #7392 only covers autosaves. here, it's more like: don't create a revision if only a post's tags have been edited.

see #11324

I've posted a patch on #7392 which I think should resolve both of these.

Adding to the list of things to consider for the 3.6 work on Revisions.

This need careful consideration and we will take a look for 3.6.

i think solving #7392 will solve this issue; the initial autosave firing is what creates the extra revision record mentioned by the author in this post. by the way, if you create a post, but go immediately to publish button from the title field without entering any content the extra revision is not created.

verified, #7392 patch fixes this.

In 1211/tests:

Revisions: When we are trying to save a new revision we shouldn't save one if the previous version was the same.

See #7392 and #9843 props adamsilverstein

In 23414:

Revisions: Before saving a new post revision make sure that something has changed in the fields that we are revisioning.

Fixes: #7392 and #9843 props adamsilverstein.

In 1214/tests:

Revisions: Add a new test for a force save filter in wp_save_post_revision

Also Update the tests to have correct ordering, be less complex and use things like assertCount.
See #7392 and #9843

In 23415:

Revisions: Allow a plugin to force us to skip the don't save this revision because it hasn't changed code if it knows better.

See #7392 and #9843. Also cleans up the whitespace.

Breaks post/revisions/test_revision_restore_caps_before_publish, since content hasn't changed a revision was not created, expected 2 revisions after second "edit", getting one.

Test was written for #16847 http://core.trac.wordpress.org/changeset/1213/tests and should probably change content the second time to create a second revision, or expect 1 revision.

alternative