Tạo Table

Table Vault

CREATE TABLE Vault (
    vault_id INTEGER PRIMARY KEY AUTOINCREMENT,
    salt TEXT NOT NULL,
    encrypted_master_key BLOB NOT NULL,
    nonce BLOB NOT NULL,
    tag BLOB NOT NULL
);

CREATE TABLE Folder (
    folder_id   INTEGER PRIMARY KEY AUTOINCREMENT, // cái này sẽ sửa lại là gán Folder ID
    vault_id    INTEGER NOT NULL,
    parent_id   INTEGER,
    name        TEXT NOT NULL,
    
    FOREIGN KEY(vault_id) REFERENCES Vault(vault_id),
    FOREIGN KEY(parent_id) REFERENCES Folder(folder_id)
);

CREATE INDEX idx_folder_lookup 
ON Folder(vault_id, parent_id, name);

Insert - Tạo folder

Tạo root folder

INSERT INTO Folder (vault_id, parent_id, name)
VALUES (1, NULL, 'photos');

Tạo folder con

INSERT INTO Folder (vault_id, parent_id, name)
VALUES (1, 1, '2024');

SELECT

Tìm folder theo path từng bước

Step 1: Tìm Root

SELECT folder_id 
FROM Folder 
WHERE vault_id = 1 
  AND parent_id IS NULL 
  AND name = 'photos';

Step2: Tìm folder con

SELECT folder_id 
FROM Folder 
WHERE parent_id = ? 
  AND name = '2024';

Lấy danh sách folder con

SELECT folder_id, name 
FROM Folder 
WHERE parent_id = ?;

UPDATE - rename/remove

Rename

UPDATE Folder 
SET name = 'documents' 
WHERE folder_id = 4;

Move Folder

UPDATE Folder 
SET parent_id = 1 
WHERE folder_id = 5;

DELETE

DELETE FROM Folder 
WHERE folder_id = ?;

Nếu muốn xóa cả sub

WITH RECURSIVE subfolders(id) AS (
    SELECT folder_id FROM Folder WHERE folder_id = ?
    UNION ALL
    SELECT f.folder_id
    FROM Folder f
    JOIN subfolders s ON f.parent_id = s.id
)
DELETE FROM Folder WHERE folder_id IN subfolders;

Workflow thực tế

Bước 1: User mở Vault

SELECT * FROM Vault WHERE vault_id = 1;

Bước 2: Lấy Root Folders

SELECT * 
FROM Folder 
WHERE vault_id = 1 AND parent_id IS NULL;