Last active
October 28, 2020 07:56
-
-
Save frnhr/4219792 to your computer and use it in GitHub Desktop.
clean database after qTranslate uninstall
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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`; | |
Excellent!
It didn't work for me...
Nice script! I've just forked to work with the square bracket language tag.
Excellent. Nice job!
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
good job!