44 lines
1.6 KiB
SQL
Executable File
44 lines
1.6 KiB
SQL
Executable File
-- Migration 001: Add RAM statistics and SMART data table
|
|
-- Date: 2025-12-07
|
|
-- Description: Adds used_mb, free_mb, shared_mb to hardware_snapshots and creates disk_smart_data table
|
|
|
|
-- Add new RAM columns to hardware_snapshots
|
|
ALTER TABLE hardware_snapshots ADD COLUMN ram_used_mb INTEGER;
|
|
ALTER TABLE hardware_snapshots ADD COLUMN ram_free_mb INTEGER;
|
|
ALTER TABLE hardware_snapshots ADD COLUMN ram_shared_mb INTEGER;
|
|
|
|
-- Create disk_smart_data table
|
|
CREATE TABLE IF NOT EXISTS disk_smart_data (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
hardware_snapshot_id INTEGER NOT NULL,
|
|
captured_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
-- Disk identification
|
|
device_name VARCHAR(50) NOT NULL,
|
|
model VARCHAR(255),
|
|
serial_number VARCHAR(100),
|
|
size_gb REAL,
|
|
disk_type VARCHAR(20), -- 'ssd' or 'hdd'
|
|
interface VARCHAR(50), -- 'sata', 'nvme', 'usb'
|
|
|
|
-- SMART Health Status
|
|
health_status VARCHAR(20), -- 'PASSED', 'FAILED', or NULL
|
|
temperature_celsius INTEGER,
|
|
|
|
-- Aging indicators
|
|
power_on_hours INTEGER,
|
|
power_cycle_count INTEGER,
|
|
reallocated_sectors INTEGER, -- Critical: bad sectors
|
|
pending_sectors INTEGER, -- Very critical: imminent failure
|
|
udma_crc_errors INTEGER, -- Cable/interface issues
|
|
|
|
-- SSD-specific
|
|
wear_leveling_count INTEGER, -- 0-100 (higher is better)
|
|
total_lbas_written REAL, -- Total data written
|
|
|
|
FOREIGN KEY (hardware_snapshot_id) REFERENCES hardware_snapshots(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_disk_smart_hardware_snapshot ON disk_smart_data(hardware_snapshot_id);
|
|
CREATE INDEX IF NOT EXISTS idx_disk_smart_device ON disk_smart_data(device_name);
|