Implementing Folder System in PostgreSQL
Turns out there are multiple way to implement folder system, and after doing some research I found that this approach Adjacency List Model easier to understand for me.
This was my finding when trying to implement folder system in PostgreSQL for readclip.site.
Table Folders
This query to create table "folders" table with columns for folder information, including a primary key for "folder_id" and a foreign key constraint to reference the parent folder.
CREATE TABLE folders (
folder_id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_folder_id INT,
created_at TIMESTAMP DEFAULT current_timestamp,
updated_at TIMESTAMP DEFAULT current_timestamp,
CONSTRAINT fk_parent_folder
FOREIGN KEY (parent_folder_id)
REFERENCES folders (folder_id)
);
Table files
CREATE TABLE files (
file_id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
folder_id INT,
content BYTEA,
file_type VARCHAR(50),
file_size BIGINT,
created_at TIMESTAMP DEFAULT current_timestamp,
updated_at TIMESTAMP DEFAULT current_timestamp,
CONSTRAINT fk_folder
FOREIGN KEY (folder_id)
REFERENCES folders (folder_id)
);
This query creates a "files" table to store information about files, including a primary key for "file_id" and a foreign key constraint to reference the parent folder.
Adding Index
CREATE INDEX idx_folder_parent_folder_id ON folders (parent_folder_id);
CREATE INDEX idx_file_folder_id ON files (folder_id);
These queries create indexes on the "parent_folder_id" column in the "folders" table and the "folder_id" column in the "files" table, which can help optimize query performance when filtering by these columns.
Sample data
INSERT INTO folders (name, parent_folder_id)
VALUES ('Documents', NULL);
This inserts a new folder named "Documents" with no parent folder (hence, NULL).
More Data Insertion
-- Inserting folders and files
INSERT INTO folders (name, parent_folder_id)
VALUES ('New Folder Name', 1);
INSERT INTO folders (name, parent_folder_id)
VALUES ('Download', 1);
INSERT INTO folders (name, parent_folder_id)
VALUES ('Zip', 3);
INSERT INTO files (name, folder_id, content, file_type, file_size)
VALUES ('MyFile1.txt', 1, E'file_content_here', 'txt', 12345);
INSERT INTO files (name, folder_id, content, file_type, file_size)
VALUES ('MyFile.txt', 2, E'file_content_here', 'txt', 12345);
INSERT INTO files (name, folder_id, content, file_type, file_size)
VALUES ('MyFile 4_1.txt', 4, E'file_content_here', 'txt', 12345);
INSERT INTO files (name, folder_id, content, file_type, file_size)
VALUES ('MyFile 4_1.txt', 4, E'file_content_here', 'txt', 12345);
These INSERT statements add sample data to the "folders" and "files" tables. Folders are created with specified names and parent folder IDs. Files are created with names, folder associations, content, file types, and sizes.
SELECT from Files:
SELECT * FROM files
WHERE folder_id = 4;
This query retrieves all files in the folder with a "folder_id" of 4. It returns all columns for the matching files.
Recursive Query:
WITH RECURSIVE folder_path AS (
SELECT folder_id, name, parent_folder_id
FROM folders
WHERE folder_id = 4
UNION ALL
SELECT f.folder_id, f.name, f.parent_folder_id
FROM folders f
JOIN folder_path p ON f.folder_id = p.parent_folder_id
)
SELECT folder_id, name
FROM folder_path;
This is a recursive common table expression (CTE) that retrieves the path of folders leading to the folder with "folder_id" 4. It starts with the folder itself and recursively goes up through its parent folders, building a hierarchical path. The final SELECT statement then retrieves the "folder_id" and "name" of each folder in the path.
