How can I query my "subtype" tables?

Question

I am trying to model inheritance in my database. The purpose of the database is to use it to emulate a directory structure in a desktop GUI. The directory structure is made up of nodes, which can either be a directory, document, or executable file. Each node has an icon ( the icons for the directory are determined at the application level, depending on whether they contain any other nodes).

Question

1. How can I query all of the nodes, so that the "subtypes" (document and executables) will get their additional relevant table? In other words, for every node in nodes, if there is a document with a foreign key to the node table, the row returned from the query should also have an extension field and icon name in the row.

2. Alternatively, is there a better way to structure this data? As mentioned, this is to emulate a file/directory tree, so nodes should be able to be created, deleted, edited, reordered arbitrarily by the user.

---

Below is what I actually have so far

Tables

Node

(This table is the base "type" from which document and executable "inherit")

CREATE TABLE IF NOT EXISTS node (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  type TEXT CHECK(type IN ("DIRECTORY", "EXECUTABLE", "DOCUMENT")) NOT NULL,
  siblingOrder INTEGER NOT NULL,
  parentId INTEGER,
  protected INTEGER DEFAULT 0 NOT NULL,
  UNIQUE (parentId, siblingOrder) ON CONFLICT ABORT,
  FOREIGN KEY(parentId) REFERENCES node(id)
);

Icon

CREATE TABLE IF NOT EXISTS icon (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

Document

CREATE TABLE IF NOT EXISTS document (
  id INTEGER PRIMARY KEY,
  extension TEXT NOT NULL,
  nodeId INTEGER NOT NULL UNIQUE,
  iconId INTEGER NOT NULL,
  FOREIGN KEY(nodeId) REFERENCES node(id),
  FOREIGN KEY(iconId) REFERENCES icon(id)
);

Programs

(Think of this as the installed applications / programs on a computer)

CREATE TABLE IF NOT EXISTS program (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

Executable

(Instances of programs)

CREATE TABLE IF NOT EXISTS executable (
  id INTEGER PRIMARY KEY,
  programId INTEGER NOT NULL,
  nodeId INTEGER NOT NULL UNIQUE,
  iconId INTEGER NOT NULL,
  FOREIGN KEY (programId) REFERENCES program(id),
  FOREIGN KEY(nodeId) REFERENCES node(id),
  FOREIGN KEY(iconId) REFERENCES icon(id)
);

Initial Data

INSERT INTO icon(id, name)
      VALUES
      (1, "folder.svg"),
      (2, "folderEmpty.svg"),
      (3, "terminal.svg"),
      (4, "textDocument.svg"),
      (5, "textEditor.svg"),
      (6, "spreadsheetEditor.svg"),
      (7, "spreadsheetDocument.svg");
INSERT INTO program (id, name)
  VALUES
  (1, "Text Editor"), 
  (2, "Spreadsheets"),
  (3, "Terminal");

INSERT INTO node (id, name, type, siblingOrder, parentId, protected) 
VALUES 
(1, "", "DIRECTORY", 0, null, 1),
(2, "Users", "DIRECTORY", 0, 1, 1),
(3, "sys", "DIRECTORY", 1, 1, 0),
(4, "bin", "DIRECTORY", 2, 1, 0),
(5, "Desktop", "DIRECTORY", 0, 2, 1),
(6, "New Folder", "DIRECTORY", 0, 5, 0),
(7, "config", "DOCUMENT", 0, 3, 0),
(8, "README", "DOCUMENT", 1, 5, 0),
(9, "resume", "DOCUMENT", 2, 5, 0),
(10, "Terminal", "EXECUTABLE", 3, 5, 0),
(11, "Text Editor", "EXECUTABLE", 4, 5, 0),
(12, "Spreadsheets", "EXECUTABLE", 5, 5, 0),
(13, "inventory", "DOCUMENT", 0, 6, 0),
(14, "todo", "DOCUMENT", 1, 6, 0),
(15, "nested folder", "DIRECTORY", 2, 6, 0),
(16, "nested folder 2", "DIRECTORY", 0, 15, 0),
(17, "test doc 1", "DOCUMENT", 1, 15, 0),
(18, "test doc 2", "DOCUMENT", 0, 16, 0),
(19, "hi", "DOCUMENT", 1, 16, 0);

INSERT INTO document (id, extension, nodeId, iconId)
  VALUES
  (1, "txt", 7, 4),
  (2, "txt", 8, 4),
  (3, "txt", 9, 4),
  (4, "sheet", 13, 7),
  (5, "txt", 14, 4),
  (6, "txt", 17, 4),
  (7, "txt", 18, 4),
  (8, "txt", 19, 4);

INSERT INTO executable (id, programId, nodeId, iconId)
VALUES
(1, 1, 11, 5), 
(2, 2, 12, 6), 
(3, 3, 10, 3);

My current query

Here is the current query im using, to get all nodes that are descendants of the desktop node. It is missing data specific to the subtypes though.. for example, extensions for document nodes and program names for executable nodes..

WITH RECURSIVE directoryTree (id, name, type, parentId, level, protected, siblingOrder, isDesktop) AS (
    SELECT n.id, n.name, n.type, n.parentId, -1, n.protected, n.siblingOrder, 1
    FROM node n 
    WHERE n.name="Desktop" AND n.protected=1
    
    UNION ALL
    
    SELECT n.id, n.name, n.type, n.parentId, level + 1, n.protected, n.siblingOrder, 0
    FROM node n
    JOIN directoryTree p ON p.id = n.parentId
  )
  
  SELECT n.id, n.name, n.type, n.parentId, n.level, n.protected, n.siblingOrder, n.isDesktop
  FROM directoryTree n
  ORDER BY n.level;

Updated Query

This is what I wanted. ( I still have to edit this so the joins are outside of the recursive query...)

WITH RECURSIVE directoryTree (id, name, type, parentId, level, protected, siblingOrder, isDesktop, extension, documentIcon, executableIcon) AS (
    SELECT n.id, n.name, n.type, n.parentId, -1, n.protected, n.siblingOrder, 1, d.extension, docicon.name, exeicon.name
    FROM node n 
    LEFT JOIN document d ON d.nodeId = n.id
    LEFT JOIN executable e ON e.nodeId = n.id
    LEFT JOIN icon docicon ON d.iconId = docicon.id
    LEFT JOIN icon exeicon ON e.iconId = exeicon.id
    WHERE n.name="Desktop" AND n.protected=1
    
    UNION
    
    SELECT n.id, n.name, n.type, n.parentId, level + 1, n.protected, n.siblingOrder, 0, d.extension, docicon.name, exeicon.name
    FROM node n
    LEFT JOIN document d ON d.nodeId = n.id
    LEFT JOIN executable e ON e.nodeId = n.id
    LEFT JOIN icon docicon ON d.iconId = docicon.id
    LEFT JOIN icon exeicon ON e.iconId = exeicon.id
    JOIN directoryTree p ON p.id = n.parentId
  )


SELECT * from directoryTree;

Updated Query 2

WITH RECURSIVE directoryTree (id, name, type, parentId, level, protected, siblingOrder, isDesktop) AS (
        SELECT n.id, n.name, n.type, n.parentId, -1, n.protected, n.siblingOrder, 1
        FROM node n 
        WHERE n.name="Desktop" AND n.protected=1
    
        UNION ALL
    
        SELECT n.id, n.name, n.type, n.parentId, level + 1, n.protected, n.siblingOrder, 0
        FROM node n
        JOIN directoryTree p ON p.id = n.parentId
    )
    
    SELECT 
    n.id, n.name, n.type, n.parentId, n.level, 
    n.protected, n.siblingOrder, n.isDesktop, d.extension, docicon.name AS docIcon, exeicon.name AS exeIcon
    FROM directoryTree n 
        LEFT JOIN document d ON d.nodeId = n.id
        LEFT JOIN executable e ON e.nodeId = n.id
        LEFT JOIN icon docicon ON d.iconId = docicon.id
        LEFT JOIN icon exeicon ON e.iconId = exeicon.id
        
    ORDER BY n.level;

Answer 1

Will there ever be another entity that extends the program entity (other than executable)? If not, I don't see a lot of purpose to it, and would just store the name field in the executable table instead of having a separate table.

Anyway, after you've recursively built out your hierarchy of nodes, you can just join to the tables you want to pull data for accordingly. For example if you want all executables with their names you just join to it in your final SELECT like so:

WITH RECURSIVE directoryTree (id, name, type, parentId, level, protected, siblingOrder, isDesktop) AS (
    SELECT n.id, n.name, n.type, n.parentId, -1, n.protected, n.siblingOrder, 1
    FROM node n 
    WHERE n.name="Desktop" AND n.protected=1

    UNION ALL

    SELECT n.id, n.name, n.type, n.parentId, level + 1, n.protected, n.siblingOrder, 0
    FROM node n
    JOIN directoryTree p ON p.id = n.parentId
)

SELECT n.id, n.name, n.type, n.parentId, n.level, n.protected, n.siblingOrder, n.isDesktop, e.id AS executableId, p.name AS programName
FROM directoryTree n
INNER JOIN executable e
    ON n.id = e.nodeId
INNER JOIN program p
    ON e.programId = p.id
ORDER BY n.level;