141 lines
5.1 KiB
SQL
141 lines
5.1 KiB
SQL
-- =====================================================
|
|
-- Database Schema untuk Pengering Ikan IoT
|
|
-- =====================================================
|
|
|
|
-- Create database (optional, Railway sudah provide)
|
|
-- CREATE DATABASE IF NOT EXISTS pengering_ikan;
|
|
-- USE pengering_ikan;
|
|
|
|
-- =====================================================
|
|
-- Table: sensor_data
|
|
-- Menyimpan data sensor dari ESP32
|
|
-- =====================================================
|
|
CREATE TABLE IF NOT EXISTS sensor_data (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
suhu FLOAT NOT NULL COMMENT 'Suhu dalam Celsius',
|
|
berat FLOAT NOT NULL COMMENT 'Berat dalam gram',
|
|
target FLOAT NOT NULL COMMENT 'Target berat dalam gram',
|
|
relay1 BOOLEAN DEFAULT FALSE COMMENT 'Status Relay 1 (Heater)',
|
|
relay2 BOOLEAN DEFAULT FALSE COMMENT 'Status Relay 2 (Fan)',
|
|
relay3 BOOLEAN DEFAULT FALSE COMMENT 'Status Relay 3 (Lamp)',
|
|
relay4 BOOLEAN DEFAULT FALSE COMMENT 'Status Relay 4 (Exhaust)',
|
|
status VARCHAR(50) DEFAULT 'DISCONNECTED' COMMENT 'Status koneksi ESP32',
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Waktu data diterima',
|
|
INDEX idx_timestamp (timestamp),
|
|
INDEX idx_status (status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Data sensor dari ESP32';
|
|
|
|
-- =====================================================
|
|
-- Table: status_history
|
|
-- Menyimpan history status dari ESP32
|
|
-- =====================================================
|
|
CREATE TABLE IF NOT EXISTS status_history (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
message TEXT NOT NULL COMMENT 'Status message dari ESP32',
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Waktu status diterima',
|
|
INDEX idx_timestamp (timestamp)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='History status dari ESP32';
|
|
|
|
-- =====================================================
|
|
-- Table: control_commands
|
|
-- Menyimpan history control commands
|
|
-- =====================================================
|
|
CREATE TABLE IF NOT EXISTS control_commands (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
command VARCHAR(50) NOT NULL COMMENT 'Command yang dikirim (HEATER_ON, FAN_OFF, dll)',
|
|
source VARCHAR(50) DEFAULT 'API' COMMENT 'Sumber command (API, MQTT, Manual)',
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Waktu command dikirim',
|
|
INDEX idx_timestamp (timestamp),
|
|
INDEX idx_command (command)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='History control commands';
|
|
|
|
-- =====================================================
|
|
-- Sample Data (Optional, untuk testing)
|
|
-- =====================================================
|
|
|
|
-- Insert sample sensor data
|
|
INSERT INTO sensor_data (suhu, berat, target, relay1, relay2, relay3, relay4, status) VALUES
|
|
(25.5, 1000.0, 800.0, true, true, false, false, 'CONNECTED'),
|
|
(26.0, 950.0, 800.0, true, true, false, false, 'CONNECTED'),
|
|
(26.5, 900.0, 800.0, true, true, false, false, 'CONNECTED'),
|
|
(27.0, 850.0, 800.0, true, true, false, false, 'CONNECTED'),
|
|
(27.5, 800.0, 800.0, false, false, false, false, 'COMPLETED');
|
|
|
|
-- Insert sample status history
|
|
INSERT INTO status_history (message) VALUES
|
|
('ESP32 Connected'),
|
|
('Drying process started'),
|
|
('Temperature: 25.5°C, Weight: 1000g'),
|
|
('Temperature: 27.5°C, Weight: 800g'),
|
|
('Drying process completed');
|
|
|
|
-- Insert sample control commands
|
|
INSERT INTO control_commands (command, source) VALUES
|
|
('HEATER_ON', 'API'),
|
|
('FAN_ON', 'API'),
|
|
('HEATER_OFF', 'API'),
|
|
('FAN_OFF', 'API');
|
|
|
|
-- =====================================================
|
|
-- Useful Queries
|
|
-- =====================================================
|
|
|
|
-- Get latest sensor data
|
|
-- SELECT * FROM sensor_data ORDER BY timestamp DESC LIMIT 1;
|
|
|
|
-- Get data history (last 50 records)
|
|
-- SELECT * FROM sensor_data ORDER BY timestamp DESC LIMIT 50;
|
|
|
|
-- Get status history (last 50 records)
|
|
-- SELECT * FROM status_history ORDER BY timestamp DESC LIMIT 50;
|
|
|
|
-- Get control commands history
|
|
-- SELECT * FROM control_commands ORDER BY timestamp DESC LIMIT 50;
|
|
|
|
-- Get statistics
|
|
-- SELECT
|
|
-- COUNT(*) as total_records,
|
|
-- MIN(suhu) as min_temp,
|
|
-- MAX(suhu) as max_temp,
|
|
-- AVG(suhu) as avg_temp,
|
|
-- MIN(berat) as min_weight,
|
|
-- MAX(berat) as max_weight,
|
|
-- AVG(berat) as avg_weight
|
|
-- FROM sensor_data;
|
|
|
|
-- Get data by date range
|
|
-- SELECT * FROM sensor_data
|
|
-- WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-31'
|
|
-- ORDER BY timestamp DESC;
|
|
|
|
-- Delete old data (keep last 1000 records)
|
|
-- DELETE FROM sensor_data
|
|
-- WHERE id NOT IN (
|
|
-- SELECT id FROM (
|
|
-- SELECT id FROM sensor_data ORDER BY timestamp DESC LIMIT 1000
|
|
-- ) AS temp
|
|
-- );
|
|
|
|
-- =====================================================
|
|
-- Maintenance Queries
|
|
-- =====================================================
|
|
|
|
-- Check table sizes
|
|
-- SELECT
|
|
-- table_name AS 'Table',
|
|
-- ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
|
|
-- FROM information_schema.TABLES
|
|
-- WHERE table_schema = 'pengering_ikan'
|
|
-- ORDER BY (data_length + index_length) DESC;
|
|
|
|
-- Optimize tables
|
|
-- OPTIMIZE TABLE sensor_data;
|
|
-- OPTIMIZE TABLE status_history;
|
|
-- OPTIMIZE TABLE control_commands;
|
|
|
|
-- Backup database (via mysqldump)
|
|
-- mysqldump -u root -p pengering_ikan > backup.sql
|
|
|
|
-- Restore database
|
|
-- mysql -u root -p pengering_ikan < backup.sql
|