The current convention for the COPY operation is to copy category subtrees only for each input category, but no category-item assignment. The core transformation step uses an RCTE loop discussed in the RCTEs for Recursive Modify section. Because the total number of existing category paths may be substantially higher than the number of affected paths, it is desirable to isolate the subset of affected paths. The subtrees_old CTE achieves this goal by matching the category path prefix against the path_old variable (ops).
Postprocessing involves several filtering steps. LOOP_COPY transforms the initial path set and labels new rows. The subtrees_new_base CTE filters out original paths and some of the possible duplicates among the newly created paths. The second step (subtrees_path) filters out all remaining duplicates. The final filtering stage (subtrees_new) removes already existing paths.
The rest of the code creates the remaining paths (the code is similar to the CREATE Paths section).
WITH RECURSIVE
------------------------------ PROLOGUE ------------------------------
json_ops(ops) AS (
VALUES
(json(
'[' ||
'{"op":"move", "path_old":"BAZ/bld/tcl/tests/safe00/", "path_new":"safe00/"},' ||
'{"op":"move", "path_old":"safe00/", "path_new":"safe/"},' ||
'{"op":"move", "path_old":"BAZ/dev/msys2", "path_new":"BAZ/dev/msys/"},' ||
'{"op":"move", "path_old":"BAZ/bld/tcl/tests/preEEE/", "path_new":"preEEE/"},' ||
'{"op":"move", "path_old":"safe/modules/", "path_new":"safe/modu/"},' ||
'{"op":"move", "path_old":"safe/modu/mod2/", "path_new":"safe/modu/mod3/"},' ||
'{"op":"move", "path_old":"BAZ/bld/tcl/tests/ssub00/", "path_new":"safe/ssub00/"},' ||
'{"op":"move", "path_old":"BAZ/dev/msys/mingw32/", "path_new":"BAZ/dev/msys/nix/"},' ||
'{"op":"move", "path_old":"safe/ssub00/modules/", "path_new":"safe/modules/"},' ||
'{"op":"move", "path_old":"BAZ/bld/tcl/tests/manYYY/", "path_new":"man000/"},' ||
'{"op":"move", "path_old":"BAZ/dev/msys/nix/etc/", "path_new":"BAZ/dev/msys/nix/misc/"},' ||
'{"op":"move", "path_old":"BAZ/bld/tcl/tests/manZZZ/", "path_new":"BAZ/bld/tcl/tests/man000/"},' ||
'{"op":"move", "path_old":"BAZ/bld/tcl/tests/man000/", "path_new":"man000/"},' ||
'{"op":"move", "path_old":"BAZ/bld/tcl/tests/safe11/", "path_new":"safe11/"}' ||
']'
))
),
base_ops AS (
SELECT
"key" + 1 AS opid,
json_extract(value, '$.op') AS op,
trim(json_extract(value, '$.path_old'), '/') || '/' AS rootpath_old,
trim(json_extract(value, '$.path_new'), '/') AS rootpath_new
FROM json_ops AS jo, json_each(jo.ops) AS terms
),
/********************************************************************/
--------------------------- SUBTREES LIST ----------------------------
subtrees_old AS (
SELECT opid, ascii_id, path AS path_old
FROM base_ops, categories
WHERE path_old like rootpath_old || '%'
ORDER BY opid, path_old
),
/********************************************************************/
----------------------------- COPY LOOP ------------------------------
LOOP_COPY AS (
SELECT 0 AS opid, ascii_id, path_old AS path_new
FROM subtrees_old
UNION ALL
SELECT ops.opid, ascii_id, path_new
FROM LOOP_COPY AS BUFFER, base_ops AS ops
WHERE ops.opid = BUFFER.opid + 1
UNION ALL
SELECT ops.opid, '~' || ascii_id AS ascii_id,
rootpath_new || substr(path_new, length(rootpath_old)) AS path_new
FROM LOOP_COPY AS BUFFER, base_ops AS ops
WHERE ops.opid = BUFFER.opid + 1
AND BUFFER.path_new like rootpath_old || '%'
),
/********************************************************************/
subtrees_new_base AS (
SELECT ascii_id, path_new
FROM LOOP_COPY
WHERE opid = (SELECT max(opid) FROM base_ops)
AND length(ascii_id) > 8
GROUP BY ascii_id, path_new
ORDER BY path_new
),
subtrees_path AS (
SELECT path_new FROM subtrees_new_base GROUP BY path_new
),
subtrees_new AS (
SELECT
json_extract('["' || replace(trim(path_new, '/'), '/', '", "') || '"]', '$[#-1]') AS name_new,
path_new
FROM subtrees_path LEFT JOIN categories AS cats ON path_new = path
WHERE ascii_id IS NULL
),
new_paths AS (
SELECT row_number() OVER (ORDER BY path_new) - 1 AS counter,
substr(path_new, 1, length(path_new) - length(name_new) - 1) AS prefix_new,
subtrees_new.*
FROM subtrees_new
),
------------------------- ASCII ID GENERATOR -------------------------
id_counts(id_counter) AS (SELECT count(*) FROM new_paths),
json_templates AS (SELECT '[' || replace(hex(zeroblob(id_counter*8/2-1)), '0', '0,') || '0,0]' AS json_template FROM id_counts),
char_templates(char_template) AS (VALUES ('-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_')),
ascii_ids AS (
SELECT group_concat(substr(char_template, (random() & 63) + 1, 1), '') AS ascii_id, "key"/8 AS counter
FROM char_templates, json_templates, json_each(json_templates.json_template) AS terms
GROUP BY counter
),
ids AS (
SELECT counter, ascii_id,
(unicode(substr(ascii_id, 1, 1)) << 8*7) +
(unicode(substr(ascii_id, 2, 1)) << 8*6) +
(unicode(substr(ascii_id, 3, 1)) << 8*5) +
(unicode(substr(ascii_id, 4, 1)) << 8*4) +
(unicode(substr(ascii_id, 5, 1)) << 8*3) +
(unicode(substr(ascii_id, 6, 1)) << 8*2) +
(unicode(substr(ascii_id, 7, 1)) << 8*1) +
(unicode(substr(ascii_id, 8, 1)) << 8*0) AS bin_id
FROM ascii_ids
),
/********************************************************************/
new_nodes AS (
SELECT bin_id AS id, name_new AS name, prefix_new AS prefix
FROM new_paths, ids USING (counter)
)
INSERT INTO categories (id, name, prefix)
SELECT * FROM new_nodes;