From aeb0696f457ca85b62d15ab6586c5779cf5cd97b Mon Sep 17 00:00:00 2001
From: Administrator <15274802129@163.com>
Date: Fri, 31 Jul 2026 16:56:21 +0800
Subject: [PATCH] feat(firewall): 添加访问日志功能
---
sql/firewall.sql | 92 ++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 92 insertions(+), 0 deletions(-)
diff --git a/sql/firewall.sql b/sql/firewall.sql
new file mode 100644
index 0000000..e2419e7
--- /dev/null
+++ b/sql/firewall.sql
@@ -0,0 +1,92 @@
+-- ============================================================
+-- 防火墙站点表
+-- ============================================================
+DROP TABLE IF EXISTS firewall_site;
+CREATE TABLE firewall_site (
+ ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
+ REVISION INT DEFAULT 1 COMMENT '乐观锁',
+ CREATED_BY VARCHAR(32) DEFAULT 'system' COMMENT '创建人',
+ CREATED_TIME DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
+ UPDATED_BY VARCHAR(32) DEFAULT 'system' COMMENT '更新人',
+ UPDATED_TIME DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
+ SITE_NAME VARCHAR(100) NOT NULL COMMENT '站点名称',
+ DOMAIN VARCHAR(200) NOT NULL COMMENT '站点域名',
+ NGINX_CONF VARCHAR(255) COMMENT 'Nginx配置文件路径',
+ STATUS TINYINT DEFAULT 1 COMMENT '状态：1-启用，0-禁用',
+ REMARK VARCHAR(255) COMMENT '备注',
+ PRIMARY KEY (ID),
+ UNIQUE KEY uk_domain (DOMAIN)
+) COMMENT = '防火墙站点表';
+
+-- ============================================================
+-- 国家白名单规则表
+-- ============================================================
+DROP TABLE IF EXISTS firewall_country_rule;
+CREATE TABLE firewall_country_rule (
+ ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
+ REVISION INT DEFAULT 1 COMMENT '乐观锁',
+ CREATED_BY VARCHAR(32) DEFAULT 'system' COMMENT '创建人',
+ CREATED_TIME DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
+ UPDATED_BY VARCHAR(32) DEFAULT 'system' COMMENT '更新人',
+ UPDATED_TIME DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
+ SITE_ID BIGINT NOT NULL COMMENT '站点ID，关联firewall_site表',
+ COUNTRY_CODE VARCHAR(10) NOT NULL COMMENT '国家编码（ISO 3166-1 alpha-2，如 JP/US/SG）',
+ COUNTRY_NAME VARCHAR(50) COMMENT '国家名称',
+ ENABLED TINYINT DEFAULT 1 COMMENT '状态：1-启用，0-禁用',
+ PRIMARY KEY (ID),
+ UNIQUE KEY uk_site_country (SITE_ID, COUNTRY_CODE),
+ INDEX idx_site_id (SITE_ID)
+) COMMENT = '防火墙国家白名单规则表';
+
+-- ============================================================
+-- IP白名单表
+-- ============================================================
+DROP TABLE IF EXISTS firewall_ip_white;
+CREATE TABLE firewall_ip_white (
+ ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
+ REVISION INT DEFAULT 1 COMMENT '乐观锁',
+ CREATED_BY VARCHAR(32) DEFAULT 'system' COMMENT '创建人',
+ CREATED_TIME DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
+ UPDATED_BY VARCHAR(32) DEFAULT 'system' COMMENT '更新人',
+ UPDATED_TIME DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
+ SITE_ID BIGINT NOT NULL COMMENT '站点ID',
+ IP VARCHAR(64) NOT NULL COMMENT 'IP地址（支持IPv4/IPv6）',
+ REMARK VARCHAR(255) COMMENT '备注',
+ EXPIRE_TIME DATETIME COMMENT '过期时间，NULL表示永久有效',
+ ENABLED TINYINT DEFAULT 1 COMMENT '状态：1-启用，0-禁用',
+ PRIMARY KEY (ID),
+ UNIQUE KEY uk_site_ip (SITE_ID, IP),
+ INDEX idx_ip (IP)
+) COMMENT = '防火墙IP白名单表';
+
+-- ============================================================
+-- 访问日志表
+-- ============================================================
+DROP TABLE IF EXISTS firewall_access_log;
+CREATE TABLE firewall_access_log (
+ ID BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
+ REVISION INT DEFAULT 1 COMMENT '乐观锁',
+ CREATED_BY VARCHAR(32) DEFAULT 'system' COMMENT '创建人',
+ CREATED_TIME DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
+ UPDATED_BY VARCHAR(32) DEFAULT 'system' COMMENT '更新人',
+ UPDATED_TIME DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
+ SITE_ID BIGINT COMMENT '站点ID',
+ IP VARCHAR(64) NOT NULL COMMENT '访问者IP',
+ COUNTRY_CODE VARCHAR(10) COMMENT '国家编码（ISO 3166-1 alpha-2）',
+ COUNTRY_NAME VARCHAR(50) COMMENT '国家名称',
+ USER_AGENT VARCHAR(500) COMMENT '浏览器UA',
+ REQUEST_URI VARCHAR(500) COMMENT '请求路径',
+ IS_BLOCKED TINYINT DEFAULT 0 COMMENT '是否被拦截：1-是，0-否',
+ PRIMARY KEY (ID),
+ INDEX idx_site_id (SITE_ID),
+ INDEX idx_ip (IP),
+ INDEX idx_country (COUNTRY_CODE),
+ INDEX idx_created_time (CREATED_TIME),
+ INDEX idx_blocked (IS_BLOCKED)
+) COMMENT = '防火墙访问日志表';
+
+-- ----------------------------
+-- 默认数据
+-- ----------------------------
+INSERT INTO firewall_site (SITE_NAME, DOMAIN, STATUS) VALUES
+('默认站点', 'shop.xxx.com', 1);
--
Gitblit v1.9.1