Create a gist now

Instantly share code, notes, and snippets.

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`;

good job!

bhcruz commented May 25, 2016

Excellent!

It didn't work for me...

nr1q commented Aug 9, 2017

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

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