d8 Migrate Book Links Hierarchy - pierregermain/MyDrupal GitHub Wiki

-- Level 1
INSERT INTO D8.`book` (`nid`, `bid`, `pid`, `has_children`, `weight`, `depth`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`)
SELECT DISTINCT
    SUBSTRING(book.link_path, 6, 6) AS NID,
    SUBSTRING(book.menu_name, 10, 6) AS BID,
    book.plid AS PID,
    book.has_children AS has_children,
    book.weight AS weight,
    book.depth AS depth,
    SUBSTRING(m1.link_path, 6, 6) AS P1,
    book.p2 AS P2,
    book.p3 AS P3,
    book.p4 AS P4,
    book.p5 AS P5,
    book.p6 AS P6,
    book.p7 AS P7,
    book.p8 AS P8,
    book.p9 AS P9
FROM
    D6.`menu_links` AS book,
    `menu_links` AS m1
WHERE
    book.module = 'book'
    AND m1.module = 'book'
    AND book.p1 = m1.mlid
    and book.depth = 1

-- Level 2
INSERT INTO D8.`book`
(`nid`, `bid`, `pid`, `has_children`, `weight`, `depth`,
 `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`)
SELECT DISTINCT
    SUBSTRING(book.link_path, 6, 6) AS NID,
    SUBSTRING(book.menu_name, 10, 6) AS BID,
    SUBSTRING(father.link_path, 6, 6) AS PID,
    book.has_children AS has_children,
    book.weight AS weight,
    book.depth AS depth,
    SUBSTRING(m1.link_path, 6, 6) AS P1,
    SUBSTRING(m2.link_path, 6, 6) AS P2,
    book.p3 AS P3,
    book.p4 AS P4,
    book.p5 AS P5,
    book.p6 AS P6,
    book.p7 AS P7,
    book.p8 AS P8,
    book.p9 AS P9
FROM
    D6.`menu_links` AS book,
    `menu_links` AS father,
    `menu_links` AS m1,
    `menu_links` AS m2
WHERE
    book.module = 'book'
    AND father.module = 'book'
    AND m1.module = 'book'
    AND book.plid = father.mlid
    AND book.p1 = m1.mlid
    AND book.p2 = m2.mlid
    AND m2.module = 'book'
    and book.depth = 2

-- Level 3
INSERT INTO D8.`book`
(`nid`, `bid`, `pid`, `has_children`, `weight`, `depth`,
 `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`)
SELECT DISTINCT
    SUBSTRING(book.link_path, 6, 6) AS NID,
    SUBSTRING(book.menu_name, 10, 6) AS BID,
    SUBSTRING(father.link_path, 6, 6) AS PID,
    book.has_children AS has_children,
    book.weight AS weight,
    book.depth AS depth,
    SUBSTRING(m1.link_path, 6, 6) AS P1,
    SUBSTRING(m2.link_path, 6, 6) AS P2,
    SUBSTRING(m3.link_path, 6, 6) AS P3,
    book.p4 AS P4,
    book.p5 AS P5,
    book.p6 AS P6,
    book.p7 AS P7,
    book.p8 AS P8,
    book.p9 AS P9
FROM
    D6.`menu_links` AS book,
    `menu_links` AS father,
    `menu_links` AS m1,
    `menu_links` AS m2,
    `menu_links` AS m3
WHERE
    book.module = 'book'
    AND father.module = 'book'
    AND m1.module = 'book'
    -- AND book.link_path = 'node/395'
    AND book.plid = father.mlid
    AND book.p1 = m1.mlid
    AND book.p2 = m2.mlid
    AND m2.module = 'book'
    AND book.p3 = m3.mlid
    AND m3.module = 'book'
    and book.depth = 3

-- Level 4
INSERT INTO D8.`book` (`nid`, `bid`, `pid`, `has_children`, `weight`, `depth`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`)
SELECT DISTINCT
    SUBSTRING(book.link_path, 6, 6) AS NID,
    SUBSTRING(book.menu_name, 10, 6) AS BID,
    SUBSTRING(father.link_path, 6, 6) AS PID,
    book.has_children AS has_children,
    book.weight AS weight,
    book.depth AS depth,
    SUBSTRING(m1.link_path, 6, 6) AS P1,
    SUBSTRING(m2.link_path, 6, 6) AS P2,
    SUBSTRING(m3.link_path, 6, 6) AS P3,
    SUBSTRING(m4.link_path, 6, 6) AS P4,
    book.p5 AS P5,
    book.p6 AS P6,
    book.p7 AS P7,
    book.p8 AS P8,
    book.p9 AS P9
FROM
    D6.`menu_links` AS book,
    `menu_links` AS father,
    `menu_links` AS m1,
    `menu_links` AS m2,
    `menu_links` AS m3,
    `menu_links` AS m4
WHERE
    book.module = 'book'
    AND father.module = 'book'
    AND m1.module = 'book'
    AND book.plid = father.mlid
    AND book.p1 = m1.mlid
    AND book.p2 = m2.mlid
    AND m2.module = 'book'
    AND book.p3 = m3.mlid
    AND m3.module = 'book'
    AND book.p4 = m4.mlid
    AND m4.module = 'book'
    and book.depth = 4

-- Level 5
INSERT INTO D8.`book` (`nid`, `bid`, `pid`, `has_children`, `weight`, `depth`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`)
SELECT DISTINCT
    SUBSTRING(book.link_path, 6, 6) AS NID,
    SUBSTRING(book.menu_name, 10, 6) AS BID,
    SUBSTRING(father.link_path, 6, 6) AS PID,
    book.has_children AS has_children,
    book.weight AS weight,
    book.depth AS depth,
    SUBSTRING(m1.link_path, 6, 6) AS P1,
    SUBSTRING(m2.link_path, 6, 6) AS P2,
    SUBSTRING(m3.link_path, 6, 6) AS P3,
    SUBSTRING(m4.link_path, 6, 6) AS P4,
    SUBSTRING(m5.link_path, 6, 6) AS P5,
    book.p6 AS P6,
    book.p7 AS P7,
    book.p8 AS P8,
    book.p9 AS P9
FROM
    D6.`menu_links` AS book,
    `menu_links` AS father,
    `menu_links` AS m1,
    `menu_links` AS m2,
    `menu_links` AS m3,
    `menu_links` AS m4,
    `menu_links` AS m5
WHERE
    book.module = 'book'
    AND father.module = 'book'
    AND m1.module = 'book'
    AND book.link_path <> 'node/1985'
    AND book.plid = father.mlid
    AND book.p1 = m1.mlid
    AND book.p2 = m2.mlid
    AND m2.module = 'book'
    AND book.p3 = m3.mlid
    AND m3.module = 'book'
    AND book.p4 = m4.mlid
    AND m4.module = 'book'
    AND book.p5 = m5.mlid
    AND m5.module = 'book'
    and book.depth = 5

-- Level 6
INSERT INTO D8.`book` (`nid`, `bid`, `pid`, `has_children`, `weight`, `depth`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`)
SELECT DISTINCT
    SUBSTRING(book.link_path, 6, 6) AS NID,
    SUBSTRING(book.menu_name, 10, 6) AS BID,
    SUBSTRING(father.link_path, 6, 6) AS PID,
    book.has_children AS has_children,
    book.weight AS weight,
    book.depth AS depth,
    SUBSTRING(m1.link_path, 6, 6) AS P1,
    SUBSTRING(m2.link_path, 6, 6) AS P2,
    SUBSTRING(m3.link_path, 6, 6) AS P3,
    SUBSTRING(m4.link_path, 6, 6) AS P4,
    SUBSTRING(m5.link_path, 6, 6) AS P5,
    SUBSTRING(m6.link_path, 6, 6) AS P6,
    book.p7 AS P7,
    book.p8 AS P8,
    book.p9 AS P9
FROM
    D6.`menu_links` AS book,
    `menu_links` AS father,
    `menu_links` AS m1,
    `menu_links` AS m2,
    `menu_links` AS m3,
    `menu_links` AS m4,
    `menu_links` AS m5,
    `menu_links` AS m6
WHERE
    book.module = 'book'
    AND father.module = 'book'
    AND m1.module = 'book'
    AND book.plid = father.mlid
    AND book.p1 = m1.mlid
    AND book.p2 = m2.mlid
    AND m2.module = 'book'
    AND book.p3 = m3.mlid
    AND m3.module = 'book'
    AND book.p4 = m4.mlid
    AND m4.module = 'book'
    AND book.p5 = m5.mlid
    AND m5.module = 'book'
    AND book.p6 = m6.mlid
    AND m6.module = 'book'
    and book.depth = 6


-- Level 7
INSERT INTO D8.`book` (`nid`, `bid`, `pid`, `has_children`, `weight`, `depth`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`)
SELECT DISTINCT
    SUBSTRING(book.link_path, 6, 6) AS NID,
    SUBSTRING(book.menu_name, 10, 6) AS BID,
    SUBSTRING(father.link_path, 6, 6) AS PID,
    book.has_children AS has_children,
    book.weight AS weight,
    book.depth AS depth,
    SUBSTRING(m1.link_path, 6, 6) AS P1,
    SUBSTRING(m2.link_path, 6, 6) AS P2,
    SUBSTRING(m3.link_path, 6, 6) AS P3,
    SUBSTRING(m4.link_path, 6, 6) AS P4,
    SUBSTRING(m5.link_path, 6, 6) AS P5,
    SUBSTRING(m6.link_path, 6, 6) AS P6,
    SUBSTRING(m7.link_path, 6, 6) AS P7,
    book.p8 AS P8,
    book.p9 AS P9
FROM
    D6.`menu_links` AS book,
    `menu_links` AS father,
    `menu_links` AS m1,
    `menu_links` AS m2,
    `menu_links` AS m3,
    `menu_links` AS m4,
    `menu_links` AS m5,
    `menu_links` AS m6,
    `menu_links` AS m7
WHERE
    book.module = 'book'
    AND father.module = 'book'
    AND m1.module = 'book'
    AND book.plid = father.mlid
    AND book.p1 = m1.mlid
    AND book.p2 = m2.mlid
    AND m2.module = 'book'
    AND book.p3 = m3.mlid
    AND m3.module = 'book'
    AND book.p4 = m4.mlid
    AND m4.module = 'book'
    AND book.p5 = m5.mlid
    AND m5.module = 'book'
    AND book.p6 = m6.mlid
    AND m6.module = 'book'
    AND book.p7 = m7.mlid
    AND m7.module = 'book'
    and book.depth = 7

-- Level 8
INSERT INTO D8.`book` (`nid`, `bid`, `pid`, `has_children`, `weight`, `depth`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`)
SELECT DISTINCT
    SUBSTRING(book.link_path, 6, 6) AS NID,
    SUBSTRING(book.menu_name, 10, 6) AS BID,
    SUBSTRING(father.link_path, 6, 6) AS PID,
    book.has_children AS has_children,
    book.weight AS weight,
    book.depth AS depth,
    SUBSTRING(m1.link_path, 6, 6) AS P1,
    SUBSTRING(m2.link_path, 6, 6) AS P2,
    SUBSTRING(m3.link_path, 6, 6) AS P3,
    SUBSTRING(m4.link_path, 6, 6) AS P4,
    SUBSTRING(m5.link_path, 6, 6) AS P5,
    SUBSTRING(m6.link_path, 6, 6) AS P6,
    SUBSTRING(m7.link_path, 6, 6) AS P7,
    SUBSTRING(m8.link_path, 6, 6) AS P8,
    book.p9 AS P9
FROM
    D6.`menu_links` AS book,
    `menu_links` AS father,
    `menu_links` AS m1,
    `menu_links` AS m2,
    `menu_links` AS m3,
    `menu_links` AS m4,
    `menu_links` AS m5,
    `menu_links` AS m6,
    `menu_links` AS m7,
    `menu_links` AS m8
WHERE
    book.module = 'book'
    AND father.module = 'book'
    AND m1.module = 'book'
    AND book.plid = father.mlid
    AND book.p1 = m1.mlid
    AND book.p2 = m2.mlid
    AND m2.module = 'book'
    AND book.p3 = m3.mlid
    AND m3.module = 'book'
    AND book.p4 = m4.mlid
    AND m4.module = 'book'
    AND book.p5 = m5.mlid
    AND m5.module = 'book'
    AND book.p6 = m6.mlid
    AND m6.module = 'book'
    AND book.p7 = m7.mlid
    AND m7.module = 'book'
    AND book.p8 = m8.mlid
    AND m8.module = 'book'
    and book.depth = 8

-- Level 9
INSERT INTO D8.`book` (`nid`, `bid`, `pid`, `has_children`, `weight`, `depth`, `p1`, `p2`, `p3`, `p4`, `p5`, `p6`, `p7`, `p8`, `p9`)
SELECT DISTINCT
    SUBSTRING(book.link_path, 6, 6) AS NID,
    SUBSTRING(book.menu_name, 10, 6) AS BID,
    SUBSTRING(father.link_path, 6, 6) AS PID,
    book.has_children AS has_children,
    book.weight AS weight,
    book.depth AS depth,
    SUBSTRING(m1.link_path, 6, 6) AS P1,
    SUBSTRING(m2.link_path, 6, 6) AS P2,
    SUBSTRING(m3.link_path, 6, 6) AS P3,
    SUBSTRING(m4.link_path, 6, 6) AS P4,
    SUBSTRING(m5.link_path, 6, 6) AS P5,
    SUBSTRING(m6.link_path, 6, 6) AS P6,
    SUBSTRING(m7.link_path, 6, 6) AS P7,
    SUBSTRING(m8.link_path, 6, 6) AS P8,
    SUBSTRING(m9.link_path, 6, 6) AS P9
FROM
    D6.`menu_links` AS book,
    `menu_links` AS father,
    `menu_links` AS m1,
    `menu_links` AS m2,
    `menu_links` AS m3,
    `menu_links` AS m4,
    `menu_links` AS m5,
    `menu_links` AS m6,
    `menu_links` AS m7,
    `menu_links` AS m8,
    `menu_links` AS m9
WHERE
    book.module = 'book'
    AND father.module = 'book'
    AND m1.module = 'book'
    AND book.plid = father.mlid
    AND book.p1 = m1.mlid
    AND book.p2 = m2.mlid
    AND m2.module = 'book'
    AND book.p3 = m3.mlid
    AND m3.module = 'book'
    AND book.p4 = m4.mlid
    AND m4.module = 'book'
    AND book.p5 = m5.mlid
    AND m5.module = 'book'
    AND book.p6 = m6.mlid
    AND m6.module = 'book'
    AND book.p7 = m7.mlid
    AND m7.module = 'book'
    AND book.p8 = m8.mlid
    AND m8.module = 'book'
    AND book.p9 = m9.mlid
    AND m9.module = 'book'
    and book.depth = 9