274 lines
12 KiB
Plaintext
274 lines
12 KiB
Plaintext
-- ============================================================================
|
|
-- COMPLETE OPTIMIZATION DATABASE MIGRATION
|
|
-- Database: machine_data
|
|
-- Version: 2.0 - Full Implementation
|
|
-- Description: Creates all tables needed for KPI tracking optimization
|
|
-- ============================================================================
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- Table 1: KPI Snapshots (Time-series data for graphs)
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS kpi_snapshots (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
timestamp BIGINT NOT NULL COMMENT 'Unix timestamp in milliseconds',
|
|
work_order_id VARCHAR(255),
|
|
oee_percent DECIMAL(5,2) DEFAULT 0,
|
|
availability_percent DECIMAL(5,2) DEFAULT 0,
|
|
performance_percent DECIMAL(5,2) DEFAULT 0,
|
|
quality_percent DECIMAL(5,2) DEFAULT 0,
|
|
cycle_count INT DEFAULT 0,
|
|
good_parts INT DEFAULT 0,
|
|
scrap_count INT DEFAULT 0,
|
|
operating_time DECIMAL(10,2) DEFAULT 0 COMMENT 'Accumulated seconds in state 1',
|
|
downtime DECIMAL(10,2) DEFAULT 0 COMMENT 'Accumulated seconds in state 0 while tracking',
|
|
machine_state INT DEFAULT 0 COMMENT 'Current machine state: 0 or 1',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_timestamp (timestamp),
|
|
INDEX idx_work_order (work_order_id),
|
|
INDEX idx_created_at (created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='KPI data snapshots for trending and graphs';
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- Table 2: Alert History (Manual + Automatic alerts)
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS alert_history (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
timestamp BIGINT NOT NULL COMMENT 'Unix timestamp in milliseconds',
|
|
alert_type VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
severity VARCHAR(20) NOT NULL COMMENT 'info, warning, critical',
|
|
source VARCHAR(50) NOT NULL COMMENT 'manual or automatic',
|
|
work_order_id VARCHAR(255),
|
|
acknowledged BOOLEAN DEFAULT 0,
|
|
acknowledged_at BIGINT,
|
|
acknowledged_by VARCHAR(100),
|
|
auto_resolved BOOLEAN DEFAULT 0,
|
|
resolved_at BIGINT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_timestamp (timestamp),
|
|
INDEX idx_severity (severity),
|
|
INDEX idx_acknowledged (acknowledged),
|
|
INDEX idx_work_order (work_order_id),
|
|
INDEX idx_source (source)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Alert history for both manual and automatic alerts';
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- Table 3: Shift Definitions (Reference data)
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS shift_definitions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
shift_name VARCHAR(50) NOT NULL,
|
|
start_hour INT NOT NULL COMMENT '0-23',
|
|
start_minute INT NOT NULL DEFAULT 0,
|
|
end_hour INT NOT NULL COMMENT '0-23',
|
|
end_minute INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
UNIQUE KEY unique_shift_name (shift_name)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Shift definitions for time range filtering';
|
|
|
|
-- Seed shift data
|
|
INSERT IGNORE INTO shift_definitions (id, shift_name, start_hour, start_minute, end_hour, end_minute) VALUES
|
|
(1, 'Day Shift', 6, 0, 15, 0),
|
|
(2, 'Evening Shift', 15, 0, 23, 0),
|
|
(3, 'Night Shift', 23, 0, 6, 0);
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- Table 4: Session State (For crash recovery - Issue 1)
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS session_state (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
session_key VARCHAR(50) NOT NULL COMMENT 'Always "current_session" - single row table',
|
|
work_order_id VARCHAR(255),
|
|
cycle_count INT DEFAULT 0,
|
|
production_start_time BIGINT COMMENT 'Unix timestamp when tracking started',
|
|
operating_time DECIMAL(10,2) DEFAULT 0,
|
|
downtime DECIMAL(10,2) DEFAULT 0,
|
|
last_update_time BIGINT,
|
|
tracking_enabled BOOLEAN DEFAULT 0,
|
|
machine_state INT DEFAULT 0,
|
|
scrap_prompt_issued_for VARCHAR(255),
|
|
current_session_id VARCHAR(100) COMMENT 'Links to production_sessions',
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
UNIQUE KEY unique_session (session_key)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Current session state for crash recovery';
|
|
|
|
-- Initialize the single session row
|
|
INSERT IGNORE INTO session_state (session_key, work_order_id, cycle_count, tracking_enabled)
|
|
VALUES ('current_session', NULL, 0, 0);
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- Table 5: Stop Events (Issue 4 - Intelligent Downtime Categorization)
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS stop_events (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
work_order_id VARCHAR(255),
|
|
session_id VARCHAR(100) COMMENT 'Links to production_sessions',
|
|
stop_time BIGINT NOT NULL COMMENT 'Unix timestamp when stop occurred',
|
|
resume_time BIGINT COMMENT 'Unix timestamp when resumed (NULL if not resumed yet)',
|
|
duration DECIMAL(10,2) COMMENT 'Duration in seconds (calculated when resumed)',
|
|
reason_category VARCHAR(20) NOT NULL COMMENT 'planned or unplanned',
|
|
reason_detail VARCHAR(100) NOT NULL COMMENT 'Specific reason from predefined list',
|
|
affects_availability BOOLEAN NOT NULL COMMENT 'TRUE for unplanned, FALSE for planned',
|
|
operator_notes TEXT COMMENT 'Additional notes from operator',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_work_order (work_order_id),
|
|
INDEX idx_session (session_id),
|
|
INDEX idx_stop_time (stop_time),
|
|
INDEX idx_category (reason_category)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tracks all stop events with categorization';
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- Table 6: Production Sessions (Issue 5 - Session Management)
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS production_sessions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
session_id VARCHAR(100) UNIQUE NOT NULL COMMENT 'UUID for this session',
|
|
work_order_id VARCHAR(255),
|
|
start_time BIGINT NOT NULL COMMENT 'Unix timestamp when session started',
|
|
end_time BIGINT COMMENT 'Unix timestamp when session ended',
|
|
duration DECIMAL(10,2) COMMENT 'Duration in seconds',
|
|
cycles_completed INT DEFAULT 0,
|
|
reason_for_start VARCHAR(50) COMMENT 'initial_start, resume_after_planned, resume_after_unplanned',
|
|
reason_for_end VARCHAR(50) COMMENT 'planned_stop, unplanned_stop, work_order_complete',
|
|
operating_time DECIMAL(10,2) DEFAULT 0 COMMENT 'Time spent in production',
|
|
downtime DECIMAL(10,2) DEFAULT 0 COMMENT 'Downtime during this session',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_work_order (work_order_id),
|
|
INDEX idx_session (session_id),
|
|
INDEX idx_start_time (start_time)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tracks production sessions for pattern analysis';
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- Table 7: Cycle Anomalies (Issue 3 - Hardware Irregularity Tracking)
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS cycle_anomalies (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
work_order_id VARCHAR(255),
|
|
session_id VARCHAR(100),
|
|
cycle_number INT NOT NULL,
|
|
expected_time DECIMAL(10,2) COMMENT 'Expected cycle time in seconds',
|
|
actual_time DECIMAL(10,2) COMMENT 'Actual cycle time in seconds',
|
|
deviation_percent DECIMAL(5,2) COMMENT 'Percentage deviation from expected',
|
|
anomaly_type VARCHAR(50) COMMENT 'slower, faster, irregular',
|
|
timestamp BIGINT NOT NULL COMMENT 'Unix timestamp when anomaly occurred',
|
|
notes TEXT COMMENT 'System-generated notes',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
INDEX idx_work_order (work_order_id),
|
|
INDEX idx_session (session_id),
|
|
INDEX idx_timestamp (timestamp),
|
|
INDEX idx_anomaly_type (anomaly_type)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tracks cycle time anomalies for diagnostics';
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- Table 8: Update work_orders table (add new columns)
|
|
-- ----------------------------------------------------------------------------
|
|
-- Add scrap_count column if it doesn't exist
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = 'machine_data'
|
|
AND TABLE_NAME = 'work_orders'
|
|
AND COLUMN_NAME = 'scrap_count';
|
|
|
|
SET @query = IF(@col_exists = 0,
|
|
'ALTER TABLE work_orders ADD COLUMN scrap_count INT DEFAULT 0 AFTER good_parts',
|
|
'SELECT "Column scrap_count already exists" AS Info');
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Add session tracking columns
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = 'machine_data'
|
|
AND TABLE_NAME = 'work_orders'
|
|
AND COLUMN_NAME = 'total_sessions';
|
|
|
|
SET @query = IF(@col_exists = 0,
|
|
'ALTER TABLE work_orders ADD COLUMN total_sessions INT DEFAULT 0',
|
|
'SELECT "Column total_sessions already exists" AS Info');
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = 'machine_data'
|
|
AND TABLE_NAME = 'work_orders'
|
|
AND COLUMN_NAME = 'total_operating_time';
|
|
|
|
SET @query = IF(@col_exists = 0,
|
|
'ALTER TABLE work_orders ADD COLUMN total_operating_time DECIMAL(10,2) DEFAULT 0',
|
|
'SELECT "Column total_operating_time already exists" AS Info');
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = 'machine_data'
|
|
AND TABLE_NAME = 'work_orders'
|
|
AND COLUMN_NAME = 'total_downtime';
|
|
|
|
SET @query = IF(@col_exists = 0,
|
|
'ALTER TABLE work_orders ADD COLUMN total_downtime DECIMAL(10,2) DEFAULT 0',
|
|
'SELECT "Column total_downtime already exists" AS Info');
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = 'machine_data'
|
|
AND TABLE_NAME = 'work_orders'
|
|
AND COLUMN_NAME = 'avg_session_duration';
|
|
|
|
SET @query = IF(@col_exists = 0,
|
|
'ALTER TABLE work_orders ADD COLUMN avg_session_duration DECIMAL(10,2) DEFAULT 0',
|
|
'SELECT "Column avg_session_duration already exists" AS Info');
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Rename legacy scrap_parts to scrap_count if it exists
|
|
SET @col_exists_legacy = 0;
|
|
SELECT COUNT(*) INTO @col_exists_legacy
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = 'machine_data'
|
|
AND TABLE_NAME = 'work_orders'
|
|
AND COLUMN_NAME = 'scrap_parts';
|
|
|
|
SET @query = IF(@col_exists_legacy > 0,
|
|
'ALTER TABLE work_orders CHANGE scrap_parts scrap_count INT DEFAULT 0',
|
|
'SELECT "No legacy scrap_parts column to rename" AS Info');
|
|
PREPARE stmt FROM @query;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- Verification Queries (Run these to confirm migration success)
|
|
-- ----------------------------------------------------------------------------
|
|
-- SELECT COUNT(*) AS kpi_snapshots_count FROM kpi_snapshots;
|
|
-- SELECT COUNT(*) AS alert_history_count FROM alert_history;
|
|
-- SELECT * FROM shift_definitions;
|
|
-- SELECT * FROM session_state WHERE session_key = 'current_session';
|
|
-- SELECT COUNT(*) AS stop_events_count FROM stop_events;
|
|
-- SELECT COUNT(*) AS production_sessions_count FROM production_sessions;
|
|
-- SELECT COUNT(*) AS cycle_anomalies_count FROM cycle_anomalies;
|
|
-- SHOW COLUMNS FROM work_orders;
|
|
|
|
-- ============================================================================
|
|
-- END OF MIGRATION
|
|
-- ============================================================================
|