113 lines
5.9 KiB
SQL
113 lines
5.9 KiB
SQL
-- 数据水印页面与 open-api 返回数据检测
|
|
|
|
-- PostgreSQL
|
|
CREATE TABLE IF NOT EXISTS d_content_watermark_setting (
|
|
id VARCHAR(64) NOT NULL,
|
|
scope_code VARCHAR(64) NOT NULL,
|
|
watermark_mode VARCHAR(32) NOT NULL DEFAULT 'NONE',
|
|
extract_enabled SMALLINT NOT NULL DEFAULT 1,
|
|
create_by VARCHAR(64) DEFAULT '',
|
|
create_time TIMESTAMP,
|
|
update_by VARCHAR(64) DEFAULT '',
|
|
update_time TIMESTAMP,
|
|
is_deleted INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (id),
|
|
CONSTRAINT uk_content_watermark_setting_scope UNIQUE (scope_code)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS d_content_watermark_rule (
|
|
id VARCHAR(64) NOT NULL,
|
|
scope_code VARCHAR(64) NOT NULL,
|
|
rule_code VARCHAR(100) NOT NULL,
|
|
rule_name VARCHAR(128) NOT NULL,
|
|
watermark_type VARCHAR(32) NOT NULL DEFAULT 'INVISIBLE',
|
|
condition_expr VARCHAR(255) DEFAULT '',
|
|
match_pattern VARCHAR(1024) NOT NULL,
|
|
action VARCHAR(20) NOT NULL DEFAULT 'ALERT',
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ENABLED',
|
|
sort_no INT NOT NULL DEFAULT 1,
|
|
create_by VARCHAR(64) DEFAULT '',
|
|
create_time TIMESTAMP,
|
|
update_by VARCHAR(64) DEFAULT '',
|
|
update_time TIMESTAMP,
|
|
is_deleted INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_content_watermark_rule_scope_status
|
|
ON d_content_watermark_rule(scope_code, status, is_deleted);
|
|
|
|
CREATE TABLE IF NOT EXISTS d_content_watermark_template (
|
|
id VARCHAR(64) NOT NULL,
|
|
scope_code VARCHAR(64) NOT NULL,
|
|
template_name VARCHAR(128) NOT NULL,
|
|
template_content VARCHAR(1024) NOT NULL,
|
|
regex_pattern VARCHAR(1024) DEFAULT '',
|
|
watermark_type VARCHAR(32) NOT NULL DEFAULT 'NONE_TRACE',
|
|
user_bind SMALLINT NOT NULL DEFAULT 0,
|
|
dynamic_time SMALLINT NOT NULL DEFAULT 0,
|
|
template_type VARCHAR(32) NOT NULL DEFAULT 'CUSTOM',
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ENABLED',
|
|
sort_no INT NOT NULL DEFAULT 1,
|
|
create_by VARCHAR(64) DEFAULT '',
|
|
create_time TIMESTAMP,
|
|
update_by VARCHAR(64) DEFAULT '',
|
|
update_time TIMESTAMP,
|
|
is_deleted INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_content_watermark_template_scope_status
|
|
ON d_content_watermark_template(scope_code, status, is_deleted);
|
|
|
|
CREATE TABLE IF NOT EXISTS d_content_watermark_history (
|
|
id VARCHAR(64) NOT NULL,
|
|
scope_code VARCHAR(64) NOT NULL,
|
|
status VARCHAR(32) NOT NULL,
|
|
watermark_type VARCHAR(32) NOT NULL DEFAULT 'NONE_TRACE',
|
|
extracted_text VARCHAR(2000) DEFAULT '',
|
|
source VARCHAR(255) DEFAULT '',
|
|
trace_method VARCHAR(32) NOT NULL DEFAULT 'INPUT',
|
|
event_time TIMESTAMP,
|
|
create_by VARCHAR(64) DEFAULT '',
|
|
create_time TIMESTAMP,
|
|
update_by VARCHAR(64) DEFAULT '',
|
|
update_time TIMESTAMP,
|
|
is_deleted INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_content_watermark_history_scope_time
|
|
ON d_content_watermark_history(scope_code, event_time, is_deleted);
|
|
|
|
INSERT INTO d_content_watermark_setting(id, scope_code, watermark_mode, extract_enabled, create_by, create_time, update_by, update_time, is_deleted)
|
|
SELECT 'content_watermark_global_init', 'GLOBAL', 'NONE', 1, 'admin', CURRENT_TIMESTAMP, 'admin', CURRENT_TIMESTAMP, 0
|
|
WHERE NOT EXISTS (SELECT 1 FROM d_content_watermark_setting WHERE scope_code = 'GLOBAL' AND is_deleted = 0);
|
|
|
|
INSERT INTO d_content_watermark_template(id, scope_code, template_name, template_content, regex_pattern, watermark_type, user_bind, dynamic_time, template_type, status, sort_no, create_by, create_time, update_by, update_time, is_deleted)
|
|
SELECT 'content_watermark_template_global_1', 'GLOBAL', '标准溯源模板', 'User_ID:${user_id} | Device:${device} | Timestamp:${ts}', 'user[_-]?id\\s*[:=]\\s*([\\w-]+).*device\\s*[:=]\\s*([\\w .\\-]+).*timestamp\\s*[:=]\\s*([0-9:\\- /]+)', 'NONE_TRACE', 1, 1, 'SYSTEM', 'ENABLED', 1, 'admin', CURRENT_TIMESTAMP, 'admin', CURRENT_TIMESTAMP, 0
|
|
WHERE NOT EXISTS (SELECT 1 FROM d_content_watermark_template WHERE id = 'content_watermark_template_global_1');
|
|
|
|
-- 兼容已存在表（按需执行）
|
|
-- ALTER TABLE d_content_watermark_template ADD COLUMN IF NOT EXISTS watermark_type VARCHAR(32) DEFAULT 'NONE_TRACE';
|
|
-- ALTER TABLE d_content_watermark_template ADD COLUMN IF NOT EXISTS user_bind SMALLINT DEFAULT 0;
|
|
-- ALTER TABLE d_content_watermark_template ADD COLUMN IF NOT EXISTS dynamic_time SMALLINT DEFAULT 0;
|
|
-- ALTER TABLE d_content_watermark_template ADD COLUMN IF NOT EXISTS template_type VARCHAR(32) DEFAULT 'CUSTOM';
|
|
-- ALTER TABLE d_content_watermark_history ADD COLUMN IF NOT EXISTS watermark_type VARCHAR(32) DEFAULT 'NONE_TRACE';
|
|
-- ALTER TABLE d_content_watermark_history ADD COLUMN IF NOT EXISTS trace_method VARCHAR(32) DEFAULT 'INPUT';
|
|
|
|
-- MySQL 参考（按需执行）
|
|
-- CREATE TABLE IF NOT EXISTS d_content_watermark_setting (
|
|
-- id VARCHAR(64) NOT NULL,
|
|
-- scope_code VARCHAR(64) NOT NULL,
|
|
-- watermark_mode VARCHAR(32) NOT NULL DEFAULT 'NONE',
|
|
-- extract_enabled TINYINT(1) NOT NULL DEFAULT 1,
|
|
-- create_by VARCHAR(64) DEFAULT '',
|
|
-- create_time DATETIME,
|
|
-- update_by VARCHAR(64) DEFAULT '',
|
|
-- update_time DATETIME,
|
|
-- is_deleted INT NOT NULL DEFAULT 0,
|
|
-- PRIMARY KEY (id),
|
|
-- UNIQUE KEY uk_content_watermark_setting_scope (scope_code)
|
|
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|