ipb3_db_pre_import_cleanup.sql

-- Cleans up IPB 3 database before importing it into another forum engine
-- tbl_* are project specific, could be removed

-- Remove banned members and members with no posts
delete m
from ipb_members m
left join tbl_user u on u.id = m.member_id
where
    (
        member_banned = 1
        or posts = 0
    )
    and not (
        -- exclude active website users w/o posts
        u.wiki_count > 0 || u.comment_count > 0 || u.extension_count > 0
    );

-- Clean up private messages

delete p
from ipb_message_posts p
left join ipb_members m on p.msg_author_id = m.member_id
where m.member_id is null;

-- Clean up profile comments

delete p
from ipb_profile_comments p
inner join ipb_members m on p.comment_by_member_id = m.member_id
where m.member_id is null;

-- Clean up posts

delete p
from ipb_posts p
left join ipb_members m on p.author_id = m.member_id
where m.member_id is null;


-- Clean up profile portal

delete p
from ipb_profile_portal p
left join ipb_members m on p.pp_member_id = m.member_id
where m.member_id is null;

-- Clean up custom fields

delete p
from ipb_pfields_content p
left join ipb_members m on p.member_id = m.member_id
where m.member_id is null;


-- Clean up message topic user map

delete p
from ipb_message_topic_user_map p
left join ipb_members m on p.map_user_id = m.member_id
where m.member_id is null;

-- Clean up friends

delete p
from ipb_profile_friends p
left join ipb_members m on p.friends_member_id = m.member_id
where m.member_id is null;

-- Clean up reputation index

delete p
from ipb_reputation_index p
left join ipb_members m on p.member_id = m.member_id
where m.member_id is null;

-- Clean up tracker


delete p
from ipb_tracker p
left join ipb_members m on p.member_id = m.member_id
where m.member_id is null;


-- Remove data that is irrelevant for import

truncate ipb_core_share_links_log;
truncate ipb_core_item_markers;
truncate ipb_content_cache_posts;
truncate ipb_inline_notifications;
truncate ipb_validating;
truncate ipb_moderator_logs;
truncate ipb_conv_link;
truncate ipb_reputation_cache;
truncate ipb_task_logs;

-- Stats

SELECT table_name, table_rows
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'yiisite'
        and table_name like 'ipb_%'
order by table_rows desc;