schema.sql - master - NickLasher/AutomatedTempScanner

schema.sql
73 строки3 KB
NickLasher
upload files
01 фев 2026, 22:40
01 фев 2026, 22:400732dad
-- Database Schema for Temperature Monitoring System
-- SQL Server

-- Users table
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    username NVARCHAR(50) NOT NULL UNIQUE,
    password NVARCHAR(255) NOT NULL,
    email NVARCHAR(100) NULL,
    telegram_id NVARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE()
);

-- Devices table
CREATE TABLE devices (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    location NVARCHAR(200) NOT NULL,
    device_type NVARCHAR(50) NOT NULL,
    min_temp FLOAT NULL,
    max_temp FLOAT NULL,
    status NVARCHAR(20) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT GETDATE()
);

-- Temperature readings table
CREATE TABLE temperature_readings (
    id INT IDENTITY(1,1) PRIMARY KEY,
    device_id INT NOT NULL,
    temperature FLOAT NOT NULL,
    humidity FLOAT NULL,
    timestamp DATETIME NOT NULL DEFAULT GETDATE(),
    is_anomaly BIT NOT NULL DEFAULT 0,
    FOREIGN KEY (device_id) REFERENCES devices(id)
);

-- Notifications table
CREATE TABLE notifications (
    id INT IDENTITY(1,1) PRIMARY KEY,
    user_id INT NOT NULL,
    message NVARCHAR(500) NOT NULL,
    notification_type NVARCHAR(20) NOT NULL DEFAULT 'email',
    status NVARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    sent_at DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Analysis results table
CREATE TABLE analysis_results (
    id INT IDENTITY(1,1) PRIMARY KEY,
    device_id INT NOT NULL,
    period_start DATETIME NOT NULL,
    period_end DATETIME NOT NULL,
    avg_temperature FLOAT NULL,
    min_temperature FLOAT NULL,
    max_temperature FLOAT NULL,
    anomaly_count INT NOT NULL DEFAULT 0,
    analysis_time DATETIME NOT NULL DEFAULT GETDATE(),
    FOREIGN KEY (device_id) REFERENCES devices(id)
);

-- Indexes for faster queries
CREATE INDEX idx_readings_device_id ON temperature_readings(device_id);
CREATE INDEX idx_readings_timestamp ON temperature_readings(timestamp);
CREATE INDEX idx_devices_status ON devices(status);
CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_status ON notifications(status);
CREATE INDEX idx_users_username ON users(username);

-- Sample data (optional)
-- INSERT INTO users (username, password, email) VALUES ('admin', 'admin123', 'admin@example.com');
-- INSERT INTO devices (name, location, device_type, min_temp, max_temp) VALUES ('Sensor-1', 'Room 101', 'DS18B20', 18.0, 25.0);