90f121e14c
SHT30 온습도 모니터링 시스템 전체 소스(서버 PHP, STM32 펌웨어, SQL, 테스트). 전체 코드리뷰에서 도출된 보안 하드닝 10건 반영: - 요청 서명 HMAC-SHA256 전환(펌웨어 sig.c/서버 config.php/호스트 패리티 동시) - 재전송 방어 + 기본 API_KEY fail-closed + 디바이스 문자열 정제(api/sensor_data.php) - 오프라인 SMS 중복 발송 경합 제거(cron_heartbeat.php, 원자적 선점) - CSV 수식 주입 방지(monthly_report.php), 감사로그 회전 락(retention_cleanup.php) - 브루트포스 카운터 원자화(login.php), 예시 TOTP 비밀키 무효화, 마이그레이션 멱등화 _backup/(하드코딩 실 비밀값 포함)·config.local.php·런타임 상태는 .gitignore 제외.
95 lines
5 KiB
SQL
95 lines
5 KiB
SQL
-- =============================================================================
|
|
-- migration_drop_leak.sql - 기존 누수(v2605) 설치 → SHT30 온습도 전용 전환
|
|
--
|
|
-- ⚠️ 비가역(DROP COLUMN/TABLE) 변경입니다. 반드시 DB 전체 백업 후 실행하세요.
|
|
-- 운영 데이터(과거 누수 이벤트/사진/사고 이력)가 삭제됩니다.
|
|
--
|
|
-- 이미 v2605 스키마(sensor_log/sensor_status/sensor_metric/sms_log + leak_*)가
|
|
-- 적용된 운영 DB를 SHT30 전용으로 정리합니다. 측정 이력(sensor_metric)과 SMS
|
|
-- 이력(sms_log)은 보존합니다.
|
|
--
|
|
-- 신규 설치라면 이 파일이 아니라 sql/schema_sht30.sql 하나만 실행하세요.
|
|
-- =============================================================================
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
-- 0) sensor_metric 이 없던 구버전 대비: 없으면 생성(있으면 무시).
|
|
CREATE TABLE IF NOT EXISTS `sensor_metric` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`device_id` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`device_location` VARCHAR(100) NOT NULL DEFAULT '',
|
|
`sensor_id` TINYINT UNSIGNED NOT NULL,
|
|
`sensor_name` VARCHAR(100) NOT NULL DEFAULT '',
|
|
`metric_type` VARCHAR(30) NOT NULL DEFAULT 'sht30',
|
|
`temperature_c` DECIMAL(6,2) NULL,
|
|
`humidity_percent` DECIMAL(5,2) NULL,
|
|
`metric_status` VARCHAR(30) NOT NULL DEFAULT 'normal',
|
|
`measured_at` DATETIME NOT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
INDEX `idx_sensor_measured` (`sensor_id`, `measured_at`),
|
|
INDEX `idx_device_sensor` (`device_id`, `sensor_id`),
|
|
INDEX `idx_metric_type` (`metric_type`, `created_at`),
|
|
INDEX `idx_status_measured` (`metric_status`, `measured_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 1) 누수 전용 테이블 제거
|
|
DROP TABLE IF EXISTS `leak_photo`;
|
|
DROP TABLE IF EXISTS `leak_incident`;
|
|
|
|
-- 2) sensor_log: 누수 enum 값 정리 → event_type 재정의 (둘 다 멱등)
|
|
-- (leak_alert/leak_recovery 행이 있으면 periodic 으로 강등 후 enum 축소.
|
|
-- 이미 정리된 DB면 매칭 행이 없어 no-op, MODIFY 도 동일 enum 재적용이라 안전.)
|
|
UPDATE `sensor_log` SET `event_type` = 'periodic'
|
|
WHERE `event_type` IN ('leak_alert', 'leak_recovery');
|
|
ALTER TABLE `sensor_log`
|
|
MODIFY `event_type` ENUM('startup','periodic') NOT NULL DEFAULT 'periodic';
|
|
|
|
-- 3~4) 인덱스/컬럼 제거·추가는 "존재할 때만" 수행하여 재실행해도 오류가 없게 한다.
|
|
-- (MySQL/MariaDB 는 DROP COLUMN/INDEX 에 IF EXISTS 호환이 일정치 않으므로
|
|
-- information_schema 로 가드하는 멱등 프로시저를 사용한다.)
|
|
DROP PROCEDURE IF EXISTS _mig_drop_leak;
|
|
DELIMITER //
|
|
CREATE PROCEDURE _mig_drop_leak()
|
|
BEGIN
|
|
DECLARE db VARCHAR(64);
|
|
SELECT DATABASE() INTO db;
|
|
|
|
-- sensor_log: is_leak 인덱스/컬럼 제거, event_type 인덱스 추가
|
|
IF EXISTS (SELECT 1 FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = db AND TABLE_NAME = 'sensor_log' AND INDEX_NAME = 'idx_is_leak') THEN
|
|
ALTER TABLE `sensor_log` DROP INDEX `idx_is_leak`;
|
|
END IF;
|
|
IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = db AND TABLE_NAME = 'sensor_log' AND COLUMN_NAME = 'is_leak') THEN
|
|
ALTER TABLE `sensor_log` DROP COLUMN `is_leak`;
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = db AND TABLE_NAME = 'sensor_log' AND INDEX_NAME = 'idx_event_type') THEN
|
|
ALTER TABLE `sensor_log` ADD INDEX `idx_event_type` (`event_type`, `created_at`);
|
|
END IF;
|
|
|
|
-- sensor_status: 누수 컬럼/인덱스 제거 (offline_alerted 는 오프라인 SMS에 필요 → 유지)
|
|
IF EXISTS (SELECT 1 FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = db AND TABLE_NAME = 'sensor_status' AND INDEX_NAME = 'idx_is_leak') THEN
|
|
ALTER TABLE `sensor_status` DROP INDEX `idx_is_leak`;
|
|
END IF;
|
|
IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = db AND TABLE_NAME = 'sensor_status' AND COLUMN_NAME = 'is_leak') THEN
|
|
ALTER TABLE `sensor_status` DROP COLUMN `is_leak`;
|
|
END IF;
|
|
IF EXISTS (SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = db AND TABLE_NAME = 'sensor_status' AND COLUMN_NAME = 'leak_periodic_count') THEN
|
|
ALTER TABLE `sensor_status` DROP COLUMN `leak_periodic_count`;
|
|
END IF;
|
|
|
|
-- sensor_metric: 임계 경보 조회용 인덱스 추가(없을 때만)
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = db AND TABLE_NAME = 'sensor_metric' AND INDEX_NAME = 'idx_status_measured') THEN
|
|
ALTER TABLE `sensor_metric` ADD INDEX `idx_status_measured` (`metric_status`, `measured_at`);
|
|
END IF;
|
|
END //
|
|
DELIMITER ;
|
|
|
|
CALL _mig_drop_leak();
|
|
DROP PROCEDURE IF EXISTS _mig_drop_leak;
|