29 lines
1.5 KiB
MySQL
29 lines
1.5 KiB
MySQL
|
-- Create media_folders table for organizing uploads
|
||
|
|
CREATE TABLE IF NOT EXISTS media_folders (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
parent_id INTEGER REFERENCES media_folders(id) ON DELETE CASCADE,
|
||
|
|
path VARCHAR(1000) NOT NULL, -- Full path like /folder1/subfolder2
|
||
|
|
created_by INTEGER,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE(parent_id, name) -- Prevent duplicate folder names in same parent
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Add folder_id to uploads table
|
||
|
|
ALTER TABLE uploads ADD COLUMN IF NOT EXISTS folder_id INTEGER REFERENCES media_folders(id) ON DELETE SET NULL;
|
||
|
|
|
||
|
|
-- Create indexes for faster queries
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_folders_parent_id ON media_folders(parent_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_media_folders_path ON media_folders(path);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_uploads_folder_id ON uploads(folder_id);
|
||
|
|
|
||
|
|
-- Add is_folder and folder_name columns to handle folder-like behavior
|
||
|
|
ALTER TABLE uploads ADD COLUMN IF NOT EXISTS is_folder BOOLEAN DEFAULT FALSE;
|
||
|
|
|
||
|
|
COMMENT ON TABLE media_folders IS 'Organizes uploaded media files into folders/directories';
|
||
|
|
COMMENT ON COLUMN media_folders.name IS 'Folder name (not full path)';
|
||
|
|
COMMENT ON COLUMN media_folders.parent_id IS 'Parent folder ID for nested folders, NULL for root';
|
||
|
|
COMMENT ON COLUMN media_folders.path IS 'Full path from root (e.g., /photos/2024)';
|
||
|
|
COMMENT ON COLUMN uploads.folder_id IS 'Folder containing this file, NULL for root';
|