132 lines
5.2 KiB
SQL
132 lines
5.2 KiB
SQL
-- Create and use database
|
|
CREATE DATABASE IF NOT EXISTS ve_router_db;
|
|
USE ve_router_db;
|
|
|
|
-- Router table
|
|
CREATE TABLE IF NOT EXISTS routers (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
router_id VARCHAR(10) UNIQUE NOT NULL, -- Unique router identifier
|
|
facility VARCHAR(50) NOT NULL,
|
|
router_alias VARCHAR(50) NOT NULL,
|
|
facility_aet VARCHAR(50) NOT NULL,
|
|
openvpn_ip VARCHAR(15) NOT NULL,
|
|
router_vm_primary_ip VARCHAR(15) NOT NULL,
|
|
last_seen TIMESTAMP NOT NULL,
|
|
vpn_status_code VARCHAR(50) NOT NULL,
|
|
disk_status_code VARCHAR(50) NOT NULL,
|
|
app_status_code VARCHAR(50) NOT NULL,
|
|
license_status ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'inactive',
|
|
free_disk BIGINT NOT NULL CHECK (free_disk >= 0),
|
|
total_disk BIGINT NOT NULL CHECK (total_disk > 0),
|
|
disk_usage DECIMAL(5,2) NOT NULL CHECK (disk_usage BETWEEN 0 AND 100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Users and Authentication tables
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('admin', 'operator', 'viewer') NOT NULL DEFAULT 'viewer',
|
|
status ENUM('active', 'locked', 'disabled') NOT NULL DEFAULT 'active',
|
|
failed_login_attempts INT NOT NULL DEFAULT 0,
|
|
last_login TIMESTAMP NULL,
|
|
password_changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT valid_email CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
|
|
CONSTRAINT valid_username CHECK (username REGEXP '^[A-Za-z0-9_-]{3,50}$')
|
|
);
|
|
|
|
-- User-Router access permissions
|
|
CREATE TABLE IF NOT EXISTS user_router_access (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
router_id INT NOT NULL,
|
|
can_view BOOLEAN NOT NULL DEFAULT TRUE,
|
|
can_manage BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_user_router_access (user_id, router_id)
|
|
);
|
|
|
|
-- Session management
|
|
CREATE TABLE IF NOT EXISTS user_sessions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
session_token VARCHAR(255) UNIQUE NOT NULL,
|
|
refresh_token VARCHAR(255) NOT NULL,
|
|
ip_address VARCHAR(45) NOT NULL,
|
|
user_agent TEXT,
|
|
expires_at TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL 24 HOUR),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT unique_session_token UNIQUE(session_token),
|
|
CONSTRAINT unique_refresh_token UNIQUE(refresh_token)
|
|
);
|
|
|
|
-- Container status table
|
|
CREATE TABLE IF NOT EXISTS container_status (
|
|
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
router_id varchar(50) NOT NULL,
|
|
container_name varchar(50) NOT NULL,
|
|
status_code varchar(50) NOT NULL,
|
|
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE(router_id, container_name)
|
|
);
|
|
|
|
-- DICOM study overview table with router_id as a string reference
|
|
CREATE TABLE IF NOT EXISTS dicom_study_overview (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
router_id VARCHAR(10) NOT NULL, -- Matching VARCHAR(10) with the routers table
|
|
study_instance_uid VARCHAR(100) UNIQUE NOT NULL,
|
|
patient_id VARCHAR(50) NOT NULL,
|
|
patient_name VARCHAR(100) NOT NULL,
|
|
accession_number VARCHAR(50) NOT NULL,
|
|
study_date DATE NOT NULL,
|
|
modality VARCHAR(20) NOT NULL,
|
|
study_description VARCHAR(255),
|
|
series_instance_uid VARCHAR(100) NOT NULL,
|
|
procedure_code VARCHAR(50),
|
|
referring_physician_name VARCHAR(100),
|
|
study_status_code VARCHAR(50) NOT NULL DEFAULT 'NEW', -- Default value, ensure 'NEW' exists in status_type
|
|
association_id VARCHAR(50) NOT NULL DEFAULT 'NEW',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create tables if they don't exist
|
|
CREATE TABLE IF NOT EXISTS status_type (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
category_id VARCHAR(50),
|
|
name VARCHAR(100),
|
|
code VARCHAR(100),
|
|
description VARCHAR(150),
|
|
severity INT
|
|
);
|
|
|
|
-- History and Audit Tables (No changes required here)
|
|
CREATE TABLE IF NOT EXISTS router_status_history (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
router_id INT NOT NULL,
|
|
vpn_status_code VARCHAR(50) NOT NULL,
|
|
disk_status_code VARCHAR(50) NOT NULL,
|
|
license_status ENUM('active', 'inactive', 'suspended') NOT NULL,
|
|
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Status category table
|
|
CREATE TABLE IF NOT EXISTS status_category (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(50) UNIQUE NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
-- Additional history and settings tables as needed...
|