NotAShelf
758aba0f7a
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: I13ef3c7b03f3833e90a7bfcdb03ac0136a6a6964
19 lines
784 B
SQL
19 lines
784 B
SQL
-- Add file_mtime column to media_items table for incremental scanning
|
|
-- Stores Unix timestamp in seconds of the file's modification time
|
|
|
|
ALTER TABLE media_items ADD COLUMN file_mtime BIGINT;
|
|
|
|
-- Create index for quick mtime lookups
|
|
CREATE INDEX IF NOT EXISTS idx_media_items_file_mtime ON media_items(file_mtime);
|
|
|
|
-- Create a scan_history table to track when each directory was last scanned
|
|
CREATE TABLE IF NOT EXISTS scan_history (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
directory TEXT NOT NULL UNIQUE,
|
|
last_scan_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
files_scanned INTEGER NOT NULL DEFAULT 0,
|
|
files_changed INTEGER NOT NULL DEFAULT 0,
|
|
scan_duration_ms INTEGER
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scan_history_directory ON scan_history(directory);
|