XF 2.0 Reset Database to repeat Import

[developr]

When I setup a XF installation then I use imported data to customize everything. When I am finish I delete the imported data and rerun the import again.

Here you have the SQL commands to delete the imported data from database and set the auto_increment of specific tables to 0.

DELETE FROM xf_admin WHERE user_id > 1;
DELETE FROM xf_admin_permission_entry WHERE user_id > 1;
DELETE FROM xf_user WHERE user_id > 1;
DELETE FROM xf_user_authenticate WHERE user_id > 1;
DELETE FROM xf_user_field_value WHERE user_id > 1;
DELETE FROM xf_user_field WHERE field_id != 'skype' AND field_id != 'facebook' AND field_id != 'twitter';
DELETE FROM xf_user_field_value WHERE field_id != 'skype' AND field_id != 'facebook' AND field_id != 'twitter';
DELETE FROM xf_user_group WHERE user_group_id > 4;
DELETE FROM xf_user_group_relation WHERE user_id > 1;
DELETE FROM xf_user_option WHERE user_id > 1;
DELETE FROM xf_user_privacy WHERE user_id > 1;
DELETE FROM xf_user_profile WHERE user_id > 1;
DELETE FROM xf_moderator WHERE user_id > 1;
DELETE FROM xf_moderator_content WHERE user_id > 1;
DELETE FROM xf_permission_entry_content WHERE user_id > 1;
DELETE FROM xf_permission_combination WHERE user_id > 1;
DELETE FROM xf_permission_entry WHERE user_id > 1;
DELETE FROM xf_user_group_change WHERE user_id > 1;
DELETE FROM xf_data_registry WHERE data_key = 'importSession';

DELETE FROM xf_phrase WHERE title LIKE 'thread_prefix.%' AND addon_id = '';;
DELETE FROM xf_phrase WHERE title LIKE 'thread_prefix_%' AND addon_id = '';;
DELETE FROM xf_phrase WHERE title LIKE 'custom_bb_code_%' AND addon_id = '';
DELETE FROM xf_phrase WHERE title LIKE 'user_field_%' AND phrase_id > '5000';
DELETE FROM xf_phrase_map WHERE title LIKE 'thread_prefix.%';
DELETE FROM xf_phrase_map WHERE title LIKE 'thread_prefix_%';
DELETE FROM xf_phrase_map WHERE title LIKE 'custom_bb_code_%' AND phrase_group IS NULL;

ALTER TABLE xf_admin auto_increment = 1;
ALTER TABLE xf_admin_permission_entry auto_increment = 1;
ALTER TABLE xf_user auto_increment = 1;
ALTER TABLE xf_user_authenticate auto_increment = 1;
ALTER TABLE xf_user_group auto_increment = 1;
ALTER TABLE xf_user_group_relation auto_increment = 1;
ALTER TABLE xf_user_option auto_increment = 1;
ALTER TABLE xf_user_privacy auto_increment = 1;
ALTER TABLE xf_user_profile auto_increment = 1;
ALTER TABLE xf_phrase auto_increment = 1;
ALTER TABLE xf_phrase_map auto_increment = 1;
ALTER TABLE xf_permission_entry_content auto_increment = 1;
ALTER TABLE xf_moderator_content auto_increment = 1;
ALTER TABLE xf_moderator auto_increment = 1;
ALTER TABLE xf_permission_entry auto_increment = 1;
ALTER TABLE xf_permission_combination auto_increment = 1;

TRUNCATE xf_approval_queue;
TRUNCATE xf_attachment;
TRUNCATE xf_attachment_data;
TRUNCATE xf_bb_code;
TRUNCATE xf_captcha_question;
TRUNCATE xf_category;
TRUNCATE xf_change_log;
TRUNCATE xf_content_spam_cache;
TRUNCATE xf_conversation_master;
TRUNCATE xf_conversation_message;
TRUNCATE xf_conversation_recipient;
TRUNCATE xf_conversation_user;
TRUNCATE xf_deletion_log;
TRUNCATE xf_edit_history;
TRUNCATE xf_error_log;
TRUNCATE xf_feed;
TRUNCATE xf_file_check;
TRUNCATE xf_find_new;
TRUNCATE xf_flood_check;
TRUNCATE xf_forum;
TRUNCATE xf_link_forum;
TRUNCATE xf_forum_prefix;
TRUNCATE xf_forum_read;
TRUNCATE xf_forum_watch;
TRUNCATE xf_image_proxy;
TRUNCATE xf_image_proxy_referrer;
TRUNCATE xf_import_log;
TRUNCATE xf_ip;
TRUNCATE xf_liked_content;
TRUNCATE xf_login_attempt;
TRUNCATE xf_moderator_log;
TRUNCATE xf_news_feed;
TRUNCATE xf_node;
TRUNCATE xf_notice;
TRUNCATE xf_notice_dismissed;
TRUNCATE xf_permission_cache_content;
TRUNCATE xf_poll;
TRUNCATE xf_poll_response;
TRUNCATE xf_poll_vote;
TRUNCATE xf_post;
TRUNCATE xf_profile_post;
TRUNCATE xf_report;
TRUNCATE xf_report_comment;
TRUNCATE xf_search_index;
TRUNCATE xf_sitemap;
TRUNCATE xf_stats_daily;
TRUNCATE xf_tag;
TRUNCATE xf_tag_content;
TRUNCATE xf_tag_result_cache;
TRUNCATE xf_thread;
TRUNCATE xf_thread_prefix;
TRUNCATE xf_thread_prefix_group;
TRUNCATE xf_thread_read;
TRUNCATE xf_thread_user_post;
TRUNCATE xf_thread_watch;
TRUNCATE xf_user_trophy;
TRUNCATE xf_user_alert;
TRUNCATE xf_user_ban;
TRUNCATE xf_user_follow;
TRUNCATE xf_user_ignored;
TRUNCATE xf_user_remember;
TRUNCATE xf_user_trophy;
TRUNCATE xf_warning;

TRUNCATE xf_mg_media_item;
TRUNCATE xf_mg_album;
TRUNCATE xf_mg_album_comment_read;
TRUNCATE xf_mg_album_watch;
TRUNCATE xf_mg_comment;
TRUNCATE xf_mg_media_comment_read;
TRUNCATE xf_mg_media_user_view;
TRUNCATE xf_mg_media_view;
TRUNCATE xf_mg_media_watch;

Then you have to delete the attachment folders recursivly.

rm -R data/attachments/* && rm -R data/avatars/* && rm -R internal_data/attachments/*
rm -R data/xfmg/album_thumbnail/* && rm -R data/xfmg/thumbnail/*
touch data/attachments/index.html && touch data/avatars/index.html && touch internal_data/attachments/index.html
touch data/xfmg/album_thumbnail/index.html && touch data/xfmg/thumbnail/index.html

 

[Mouth]

DELETE FROM xf_permission_entry_content WHERE user_id > 1;
DELETE FROM xf_permission_combination WHERE user_id > 1;
DELETE FROM xf_permission_entry WHERE user_id > 1;
ALTER TABLE xf_permission_entry_content auto_increment = 1;
TRUNCATE xf_permission_cache_content;

Could the above be used, followed by copying the contents of xf_permission_entry* and xf_permission_combination* from a DEV environment into a PROD environment, to exactly replicate usergroup permissions from DEV into PROD?

 

[HLR]

Thank you for this! Saved me a ton of time.

For the latest XF, I had to add this:

TRUNCATE xf_reaction_content;