Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
clean database after qTranslate uninstall
# QTRANSLATE CLEANUP QUERIES
# create temp column to separate post content on <!--more--> tag to simplify queries
ALTER TABLE `wp_posts` ADD `tmp_excerpt` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
# split content
## fill `tmp_exceprt` column
UPDATE wp_posts SET tmp_excerpt =
SUBSTRING(
post_content
FROM 1
FOR LOCATE(
'<!--more-->',
post_content
) - 1
) ;
## fill strip the excerpt from `post_content` column
UPDATE wp_posts SET post_content = case when
LOCATE('<!--more-->', post_content) > 0
then
SUBSTRING(
post_content
FROM LOCATE(
'<!--more-->',
post_content
) + 11
)
else
post_content
end;
# clean the qTranslate data, leaving only english
## `post_content`
### strip after "en" content
UPDATE wp_posts SET post_content = case when
LOCATE('<!--:en-->', post_content) > 0
then
SUBSTRING(
post_content
FROM 1
FOR LOCATE(
'<!--:-->',
post_content,
LOCATE('<!--:en-->', post_content)
) - 1
)
else
post_content
end;
### strip before "en" content
UPDATE wp_posts SET post_content = case when
LOCATE('<!--:en-->', post_content) > 0
then
SUBSTRING(
post_content
FROM LOCATE(
'<!--:en-->',
post_content
) + 10
)
else
post_content
end;
## `tmp_excerpt`
### strip after "en" content
UPDATE wp_posts SET tmp_excerpt = case when
LOCATE('<!--:en-->', tmp_excerpt) > 0
then
SUBSTRING(
tmp_excerpt
FROM 1
FOR LOCATE(
'<!--:-->',
tmp_excerpt,
LOCATE('<!--:en-->', tmp_excerpt)
) - 1
)
else
tmp_excerpt
end;
### strip before "en" content
UPDATE wp_posts SET tmp_excerpt = case when
LOCATE('<!--:en-->', tmp_excerpt) > 0
then
SUBSTRING(
tmp_excerpt
FROM LOCATE(
'<!--:en-->',
tmp_excerpt
) + 10
)
else
tmp_excerpt
end;
## `post_title`
### strip after "en" content
UPDATE wp_posts SET post_title = case when
LOCATE('<!--:en-->', post_title) > 0
then
SUBSTRING(
post_title
FROM 1
FOR LOCATE(
'<!--:-->',
post_title,
LOCATE('<!--:en-->', post_title)
) - 1
)
else
post_title
end;
### strip before "en" content
UPDATE wp_posts SET post_title = case when
LOCATE('<!--:en-->', post_title) > 0
then
SUBSTRING(
post_title
FROM LOCATE(
'<!--:en-->',
post_title
) + 10
)
else
post_title
end;
## `post_excerpt`
### strip after "en" content
UPDATE wp_posts SET post_excerpt = case when
LOCATE('<!--:en-->', post_excerpt) > 0
then
SUBSTRING(
post_excerpt
FROM 1
FOR LOCATE(
'<!--:-->',
post_excerpt,
LOCATE('<!--:en-->', post_excerpt)
) - 1
)
else
post_excerpt
end;
### strip before "en" content
UPDATE wp_posts SET post_excerpt = case when
LOCATE('<!--:en-->', post_excerpt) > 0
then
SUBSTRING(
post_excerpt
FROM LOCATE(
'<!--:en-->',
post_excerpt
) + 10
)
else
post_excerpt
end;
# combine the `tmp_excerpt` back into `post_content` and clean up
## concatenate `tmp_excerpt` and `post_content` back
UPDATE wp_posts SET post_content = case when
CHAR_LENGTH(tmp_excerpt) > 0
then
CONCAT(
tmp_excerpt,
'<!--more-->',
post_content
)
else
post_content
end;
## drop the `tmp_excerpt` column
ALTER TABLE `wp_posts` DROP `tmp_excerpt`;
@piotr-elmanowski

This comment has been minimized.

Copy link

@piotr-elmanowski piotr-elmanowski commented Mar 3, 2016

good job!

@bhcruz

This comment has been minimized.

Copy link

@bhcruz bhcruz commented May 25, 2016

Excellent!

@meidanisalekos

This comment has been minimized.

Copy link

@meidanisalekos meidanisalekos commented Jul 15, 2016

It didn't work for me...

@nr1q

This comment has been minimized.

Copy link

@nr1q nr1q commented Aug 9, 2017

Nice script! I've just forked to work with the square bracket language tag.

@aecsahin

This comment has been minimized.

Copy link

@aecsahin aecsahin commented Sep 5, 2019

Excellent. Nice job!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment