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 Path,
           0 parent_len,
           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 Path,
           parent_len + LENGTH(item) + 1, 
           SUBSTR(value, 1, INSTR(value, '/') - 1),
           SUBSTR(value, INSTR(value, '/') + 1)
    FROM split
    WHERE value LIKE '%_/_%' 
  ), 
  row_numbers AS (
    SELECT parent_path, item, 
           ROW_NUMBER() OVER (ORDER BY parent_path, item) rn
    FROM (SELECT DISTINCT SUBSTR(Path, 1, parent_len) parent_path, item FROM split)       
  )
INSERT INTO dir_struct(Directory, Directory_Parent, Value)  
SELECT r1.rn, COALESCE(r2.rn, 0) rn_parent, r1.item 
FROM row_numbers r1 LEFT JOIN row_numbers r2
ON r1.parent_path = r2.parent_path || r2.item || '/'
✓
SELECT * FROM dir_struct


Directory
Directory_Parent
Value


0
null
root

1
0
documents

2
0
pictures

3
0
video

4
0
videos

5
1
legal

6
5
father

7
6
estate

8
7
newyork

9
8
albany

10
2
family

11
10
father

12
11
newyork

13
12
albany

14
13
holiday

15
14
christmas

16
15
2002

17
16
july

18
16
june

19
3
gopro

20
19
father

21
20
newyork

22
21
albany

23
22
holiday

24
23
christmas

25
24
2002

26
4
gopro

27
26
father

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