logo

한국어

XE→ Wordpress DB 마이그레이션

관리자 2018.05.29 15:44 조회 수 : 18

-- 다운로드: xe_to_wordpress_migration.sql

-----------------------------------------------------------------
--
-- hasu0707.nflint.com의 XE를 WordPress로 마이그레이션 해주는 SQL
--
-- XE: v1.9.6
-- WordPress: v4.9.6
--
-- 이존석 (hasu0707@gmail.com)
--
-----------------------------------------------------------------
 -----------------------------------------------------------------
--
-- 초기화
--
-----------------------------------------------------------------

DELETE
FROM db_wp.wp_terms;


DELETE
FROM db_wp.wp_term_taxonomy;


DELETE
FROM db_wp.wp_term_relationships;


DELETE
FROM db_wp.wp_posts;


COMMIT;


ALTER TABLE db_wp.wp_posts AUTO_INCREMENT=0;


ALTER TABLE db_wp.wp_terms AUTO_INCREMENT=0;


ALTER TABLE db_wp.wp_term_taxonomy AUTO_INCREMENT=0;


INSERT INTO db_wp.wp_terms (NAME, slug)
VALUES ("미분류",
        "uncategorized");

-----------------------------------------------------------------
--
-- 메뉴 아이템(카테고리)
-- 상위 카테고리는 수동으로 잡아줘야 한다.
--
-----------------------------------------------------------------

INSERT INTO db_wp.wp_terms (NAME, slug)
SELECT NAME,
       url
FROM db_xe.hasu0707_xe_menu_item
WHERE (menu_srl = 64
       AND position("/" IN url) = 0
       AND strcmp(url, "index") != 0)
ORDER BY NAME;


INSERT INTO db_wp.wp_term_taxonomy (term_id)
SELECT term_id
FROM db_wp.wp_terms;


UPDATE db_wp.wp_term_taxonomy
SET taxonomy="category";


UPDATE db_wp.wp_term_taxonomy
SET COUNT=1
WHERE (term_taxonomy_id=1);

-----------------------------------------------------------------
--
-- 포스팅 옮기기 (내용, 시간, 날짜)
--
-----------------------------------------------------------------

INSERT INTO db_wp.wp_posts (post_author, post_date, post_date_gmt, post_modified, post_modified_gmt, post_content, post_title, post_excerpt, to_ping, pinged, post_content_filtered, comment_count)
SELECT 1,
       Str_to_date(regdate, '%Y%m%d%H%i%s'),
       date_add(Str_to_date(regdate, '%Y%m%d%H%i%s'), interval 9 hour),
       str_to_date(last_update, '%Y%m%d%H%i%s'),
       date_add(str_to_date(last_update, '%Y%m%d%H%i%s'), interval 9 hour),
       content,
       title,
       "",
       "",
       "",
       "",
       module_srl
FROM db_xe.hasu0707_xe_documents
ORDER BY regdate ASC;

-----------------------------------------------------------------
--
-- 이미지 링크 수정
-- xe/files/attach → wordpress/wp-content/xe_files/ 밑에 복사
--
-----------------------------------------------------------------

UPDATE db_wp.wp_posts
SET post_content = Replace(post_content, '/xe/files/attach', '/wordpress/wp-content/xe_files/attach');

-----------------------------------------------------------------
--
-- 카테고리 별 부모 카테고리 지정
--
-----------------------------------------------------------------

UPDATE db_wp.wp_term_taxonomy
SET parent = CASE
                 WHEN term_id=2 THEN 8
                 WHEN term_id=10 THEN 8
                 WHEN term_id=19 THEN 8
                 WHEN term_id=21 THEN 8
                 WHEN term_id=35 THEN 8
                 WHEN term_id=43 THEN 8
                 WHEN term_id=44 THEN 8
                 WHEN term_id=47 THEN 8
                 WHEN term_id=48 THEN 8
                 WHEN term_id=9 THEN 7
                 WHEN term_id=12 THEN 7
                 WHEN term_id=17 THEN 7
                 WHEN term_id=28 THEN 7
                 WHEN term_id=30 THEN 7
                 WHEN term_id=40 THEN 7
                 WHEN term_id=18 THEN 11
                 WHEN term_id=22 THEN 11
                 WHEN term_id=23 THEN 11
                 WHEN term_id=36 THEN 11
                 WHEN term_id=37 THEN 11
                 WHEN term_id=39 THEN 11
                 WHEN term_id=49 THEN 11
                 WHEN term_id=51 THEN 11
                 WHEN term_id=53 THEN 11
                 WHEN term_id=55 THEN 11
                 WHEN term_id=56 THEN 11
                 WHEN term_id=3 THEN 32
                 WHEN term_id=4 THEN 32
                 WHEN term_id=13 THEN 32
                 WHEN term_id=14 THEN 32
                 WHEN term_id=16 THEN 32
                 WHEN term_id=31 THEN 32
                 WHEN term_id=42 THEN 32
                 WHEN term_id=46 THEN 32
                 WHEN term_id=33 THEN 32
                 WHEN term_id=5 THEN 38
                 WHEN term_id=25 THEN 38
                 WHEN term_id=27 THEN 38
                 WHEN term_id=52 THEN 38
                 WHEN term_id=54 THEN 38
                 WHEN term_id=6 THEN 41
                 WHEN term_id=20 THEN 41
                 WHEN term_id=26 THEN 41
                 WHEN term_id=29 THEN 41
                 WHEN term_id=34 THEN 41
                 WHEN term_id=45 THEN 41
                 WHEN term_id=50 THEN 41
                 ELSE 0
             END;

-----------------------------------------------------------------
--
-- 카테고리 이동
-- WHEN <XE 모듈번호> THEN <wp_term_taxonomy.term_taxonomy_id>
--
-----------------------------------------------------------------

INSERT INTO db_wp.wp_term_relationships
SELECT id,
       CASE (comment_count)
           WHEN 5056 THEN 7
           WHEN 1923 THEN 9
           WHEN 1915 THEN 17
           WHEN 4585 THEN 28
           WHEN 5060 THEN 30
           WHEN 1919 THEN 40
           WHEN 1911 THEN 12
           WHEN 1836 THEN 8
           WHEN 1852 THEN 2
           WHEN 1861 THEN 10
           WHEN 1863 THEN 19
           WHEN 4798 THEN 21
           WHEN 1869 THEN 35
           WHEN 1871 THEN 43
           WHEN 4462 THEN 44
           WHEN 5957 THEN 47
           WHEN 1873 THEN 48
           WHEN 1838 THEN 11
           WHEN 4502 THEN 18
           WHEN 1875 THEN 22
           WHEN 1877 THEN 23
           WHEN 1881 THEN 36
           WHEN 1883 THEN 37
           WHEN 1885 THEN 39
           WHEN 1887 THEN 49
           WHEN 1889 THEN 51
           WHEN 1891 THEN 53
           WHEN 1893 THEN 55
           WHEN 3937 THEN 56
           WHEN 1927 THEN 15
           WHEN 2727 THEN 24
           WHEN 5090 THEN 3
           WHEN 5094 THEN 4
           WHEN 5098 THEN 13
           WHEN 5100 THEN 14
           WHEN 5102 THEN 16
           WHEN 5096 THEN 31
           WHEN 5092 THEN 33
           WHEN 5106 THEN 42
           WHEN 5104 THEN 46
           WHEN 5088 THEN 32
           WHEN 1842 THEN 38
           WHEN 1895 THEN 5
           WHEN 1899 THEN 25
           WHEN 1901 THEN 27
           WHEN 1905 THEN 52
           WHEN 1907 THEN 54
           WHEN 1844 THEN 41
           WHEN 4093 THEN 6
           WHEN 1913 THEN 20
           WHEN 1917 THEN 26
           WHEN 6210 THEN 29
           WHEN 5048 THEN 34
           WHEN 1921 THEN 45
           WHEN 5959 THEN 50
           ELSE 0
       END,
       0
FROM db_wp.wp_posts;

-----------------------------------------------------------------
--
-- comment_count 원상복구
--
-----------------------------------------------------------------

UPDATE db_wp.wp_posts
SET comment_count = 0;

-----------------------------------------------------------------
--
-- 카테고리별 게시물 카운팅
--
-----------------------------------------------------------------

UPDATE db_wp.wp_term_taxonomy
SET COUNT =
  (SELECT COUNT(*)
   FROM db_wp.wp_term_relationships
   WHERE term_taxonomy_id=db_wp.wp_term_taxonomy.term_taxonomy_id);

-----------------------------------------------------------------
--
-- Grant 테이블을 reload 하여 변경사항 적용
--
-----------------------------------------------------------------
 FLUSH PRIVILEGES;