SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE listfile (FileDataID INTEGER, Path TEXT);
✓
INSERT INTO listfile (FileDataID, Path) VALUES
  ('1', 'videos/gopro/father/mov001.mp4'),
  ('2', 'videos/gopro/father/mov002.mp4'),
  ('3', 'pictures/family/father/Oldman.jpg'),
  ('4', 'pictures/family/father/Oldman2.jpg'),
  ('5', 'documents/legal/father/estate/will.doc'),
  ('6', 'documents/legal/father/estate/will2.doc'),
  ('7', 'documents/legal/father/estate/newyork/albany/will.doc'),
  ('8', 'video/gopro/father/newyork/albany/holiday/christmas/2002/mov001.mp4'),
  ('9', 'pictures/family/father/newyork/albany/holiday/christmas/2002/july/Oldman.jpg'),
  ('10', 'pictures/family/father/newyork/albany/holiday/christmas/2002/june/Oldman2.jpg');
✓
CREATE TABLE dir_struct (
  Directory INTEGER PRIMARY KEY, 
  Directory_Parent INTEGER REFERENCES dir_struct(Directory), 
  Value TEXT
);
✓
INSERT INTO dir_struct (Directory, Directory_Parent, Value) VALUES
  (0, null, 'root');
✓
PRAGMA foreign_keys = OFF; 
✓
WITH 
  split AS (
    SELECT 0 idx,
           FileDataID,
           SUBSTR(SUBSTR(Path, 1), 1, INSTR(SUBSTR(Path, 1), '/') - 1) item,
           SUBSTR(SUBSTR(Path, 1), INSTR(SUBSTR(Path, 1), '/') + 1) value
    FROM listfile
    UNION ALL
    SELECT idx + 1,
           FileDataID,
           SUBSTR(value, 1, INSTR(value, '/') - 1),
           SUBSTR(value, INSTR(value, '/') + 1)
    FROM split
    WHERE value LIKE '%_/_%' 
  ),
  cols AS (
    SELECT DISTINCT
           MAX(CASE WHEN idx = 0 THEN item END) path0,
           MAX(CASE WHEN idx = 1 THEN item END) path1,
           MAX(CASE WHEN idx = 2 THEN item END) path2,
           MAX(CASE WHEN idx = 3 THEN item END) path3,
           MAX(CASE WHEN idx = 4 THEN item END) path4,
           MAX(CASE WHEN idx = 5 THEN item END) path5,
           MAX(CASE WHEN idx = 6 THEN item END) path6,
           MAX(CASE WHEN idx = 7 THEN item END) path7,
           MAX(CASE WHEN idx = 8 THEN item END) path8,
           MAX(CASE WHEN idx = 9 THEN item END) path9
    FROM split
    GROUP BY FileDataID
  ),
  paths AS (
    SELECT path0, path1, path2, path3, path4, path5, path6, path7, path8, path9 FROM cols UNION
    SELECT path0, path1, path2, path3, path4, path5, path6, path7, path8, null FROM cols UNION
    SELECT path0, path1, path2, path3, path4, path5, path6, path7, null, null FROM cols UNION
    SELECT path0, path1, path2, path3, path4, path5, path6, null, null, null FROM cols UNION
    SELECT path0, path1, path2, path3, path4, path5, null, null, null, null FROM cols UNION
    SELECT path0, path1, path2, path3, path4, null, null, null, null, null FROM cols UNION
    SELECT path0, path1, path2, path3, null, null, null, null, null, null FROM cols UNION
    SELECT path0, path1, path2, null, null, null, null, null, null, null FROM cols UNION
    SELECT path0, path1, null, null, null, null, null, null, null, null FROM cols UNION
    SELECT path0, null, null, null, null, null, null, null, null, null FROM cols
  ), 
  ids AS (
    SELECT *, 
           ROW_NUMBER() OVER (ORDER BY path0, path1, path2, path3, path4, path5, path6, path7, path8, path9) nr,
           COALESCE(path9, path8, path7, path6, path5, path4, path3, path2, path1, path0) last_child,
           path0 || COALESCE('/' || path1, '') ||
                    COALESCE('/' || path2, '') ||
                    COALESCE('/' || path3, '') ||
                    COALESCE('/' || path4, '') ||
                    COALESCE('/' || path5, '') ||
                    COALESCE('/' || path6, '') ||
                    COALESCE('/' || path7, '') ||
                    COALESCE('/' || path8, '') ||
                    COALESCE('/' || path9, '') full_path
    FROM paths       
  )
INSERT INTO dir_struct(Directory, Directory_Parent, Value)
SELECT i1.nr, COALESCE(i2.nr, 0), i1.last_child
FROM ids i1 LEFT JOIN ids i2
ON i1.full_path = i2.full_path || '/' || i1.last_child
✓
SELECT * FROM dir_struct


Directory
Directory_Parent
Value


0
null
root

1
0
documents

2
1
legal

3
2
father

4
3
estate

5
4
newyork

6
5
albany

7
0
pictures

8
7
family

9
8
father

10
9
newyork

11
10
albany

12
11
holiday

13
12
christmas

14
13
2002

15
14
july

16
14
june

17
0
video

18
17
gopro

19
18
father

20
19
newyork

21
20
albany

22
21
holiday

23
22
christmas

24
23
2002

25
0
videos

26
25
gopro

27
26
father
Directory	Directory_Parent	Value
0	null	root
1	0	documents
2	1	legal
3	2	father
4	3	estate
5	4	newyork
6	5	albany
7	0	pictures
8	7	family
9	8	father
10	9	newyork
11	10	albany
12	11	holiday
13	12	christmas
14	13	2002
15	14	july
16	14	june
17	0	video
18	17	gopro
19	18	father
20	19	newyork
21	20	albany
22	21	holiday
23	22	christmas
24	23	2002
25	0	videos
26	25	gopro
27	26	father